Nested IF statements in Excel [Over the 7 allowed limit]

Question

I am trying to create a spreadsheet which automagically gives a grade to a student based on their marks they got. I've apparently hit Excel's nested IF statement limit which is 7. Here's my if statement: ``` =IF(O5>0.895,"A+",IF(O5>0.845,"A",IF(O5>0.795,"A-",IF(O5>0.745,"B+",IF(O5>0.695,"B",IF(O5>0.645,"B-",IF(O5>0.595,"C+",IF(O5>0.545,"C","D")))))))) ``` I was reading online that I could create a VBA script and assign it that, but I dont know anything about VBA....so if someone could help me write a VBA for this, would be awesome. Its still missing the C- grade and anything lower should be awarded a D mark. This is the grading scheme I am trying to create...: A+ 89.500 - 100.000 Pass with Distinction A 84.500 - 89.490 Pass with Distinction A- 79.500 - 84.490 Pass with Distinction B+ 74.500 - 79.490 Pass with Merit B 69.500 - 74.490 Pass with Merit B- 64.500 - 69.490 Pass with Merit C+ 59.500 - 64.490 Pass C 54.500 - 59.490 Pass C- 49.500 - 54.490 Pass D 0.000 - 49.490 Specified Fail…

Accepted Answer

You can do this much more elegantly with the `VLOOKUP` formula by making separate table mapping lower bounds to letters. The mapping table must be sorted by grade number ascending.

For example:

Table

```
A     B
0     D
49.5  C-
54    C
59.5  C+
...   ...
```

Formula:

```
=VLOOKUP(SomeCell, $A$1:$B$9, 2, TRUE)
```

Where `$A$1:$B$9` is the range with the grade table. (The `$` signs tell Excel not to move the reference if you copy the formula). Passing `TRUE` as the last argument will cause Excel to do a binary search to find the value, which (as long as the data is sorted) is exactly what you want it to do.

Nested IF statements in Excel [Over the 7 allowed limit]

Solution

Problem