Google Sheets – Spreadsheet Formula for Assigning Letter Grades

formulasgoogle sheetsworksheet-function

I'm trying to figure out how to write an IF statement that will be 'true' if a value is in-between two values.

Example:

If value is between 60 and 69.9 return "D"

If value is between 70 and 79.9 return "C"

If value is between 80 and 89.9 return "B"

I have this, but I can't figure out how to modify it so the test is an in-between instead of a less than or greater than.

=IF(A1<60, "F", IF(A1>60, "A", 0))

Best Answer

Keep the inequality signs going in the same direction: 

=IF(A1<60, "F", IF(A1<70, "D", IF(A1<80, "C", IF(A1<90, "B", "A"))))

The above works fine as long as there aren't too many cases. But if you had many options, C-,C+,B-,B+... it would be better to use a lookup table instead. For example, if you have a lookup table such as the one below, then 

=vlookup(A1, $K$1:$L$5)

does the same job as nested IFs. The table is also easier to maintain in case of changes: e.g., you decided to move some cutoffs. 

+---+----+---+
|   | K  | L |
+---+----+---+
| 1 |  0 | F |
| 2 | 60 | D |
| 3 | 70 | C |
| 4 | 80 | B |
| 5 | 90 | A |
+---+----+---+
Related Topic