8

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))
7

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 |
+---+----+---+
  • Isn't a third argument needed to vlookup in Google Docs? – Ellen Spertus Oct 17 '16 at 20:28
2

Another solution, taken from the Google Docs Help Forum, maintains the cut-offs directly in the formula thus doing away with maintaining them separately.

For the cut-offs defined in OP, the formula is

=VLOOKUP($A1,{0,"F";60,"D";70,"C";80,"B";90,"A"},2)

Changing the cut-offs may appear cumbersome now but can be done efficiently as follows. Change the cut-offs in the formula in the top-most cell and then fill down by doing a CMD-D or CTRL-D depending on your platform.

1

I modified @wsaleem's cut-offs to include -/+ grades (per the scale at my college):

=VLOOKUP(BB2,{0,"F";60,"D-";63,"D";68,"D+";70,"C-";73,"C";78,"C+";80,"B-";83,"B";88,"B+";90,"A-";93,"A"},2)

Make sure to ROUND() your input value. I was using AVERAGE() and though the field visibly rounded, the value was not.

  • I'm trying to convert a letter grade to a GPA number. I tried the following VLOOKUP, when the letter grade is in column O: =vlookup($O2,{F,"0";D,"1.0";C-,"1.67";C,"2.0";C+,"2.33";B-,"2.67";B,"3.0";B+,"3.33";A-,"3.67";A,"4.0"}2) I don't really know what I'm doing. What's that last "2" for after the end-bracket, but before the end-parentheses? – user205307 Jan 3 '19 at 17:14
  • You are missing a comma between the range and the 2 (these are comma-separated parameters). Check out the documentation support.google.com/docs/answer/3093318?hl=en – ow3n Jan 3 '19 at 23:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.