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

excel vba if-statement nested worksheet-function
Alks picture Alks · Mar 17, 2010 · Viewed 13.9k times · Source

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

I wouldn't mind going down the VBA route, however my understanding of VB language is absolutely minimal (don't like it)...if this gets too tedious, I was thinking to create a small php/mysql application instead.

Answer

SLaks picture SLaks · Mar 17, 2010

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.

Related questions

VBA: Conditional - Is Nothing

There is an If condition in a VBA application as seen below: If Not My_Object Is Nothing Then My_Object.Compute When the code is run in debug mode, I found that the If condition returns a true even …

Read More
Excel VBA - Skip sub or end sub IF Statement

My code loops through cell values in a table and sees if document with same name exists. If it does exist, it will perform an action to open those files and import data. If bProcess Then FileCounter = 0 For Each folderIDX …

Read More
excell 2007 macro validate data entered into cell and show msgbox if incorrect

Please can someone help with the following code. it gives me an error at the following line: Set range = "C5:L14" This is the complete code: Private Sub Worksheet_Change(ByVal Target As Excel.range) Dim ws As Worksheet Dim …

Read More