Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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))
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 |
+---+----+---+
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.
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.
