Although the VLOOKUP versus INDEX/MATCH debate gets most of the attention there are MANY more topics to debate in Excel. When calculating student grades what method is best: NESTED IF  or  LOOKUP/BINNING TABLE?

 

Quick Refresher

A group of students write a test and get a numerical score out of 100. We want to convert these numerical scores into a letter grade like A+, A, A-, B+, B, B-, etc.

Some solutions are easier if we only have grades A, B, C, D, F but often we have to include the + and – grades. It would look something like this:

 

Follow Along With My Excel File

Get the Excel file here or from my One Drive here (“Nested If  versus Lookup Table v6.xlsx”) 

 

How Do We Define ‘Best’?

How do we judge the solutions? Here are some possible factors to consider:

  • Quickest solution to create
  • Easiest solution to understand/audit
  • Easiest solution to modify
  • Quickest solution to calculate
  • Least error prone solution

 

Solution 1  Long NESTED IF

=IF(A3>=98,”A+”,IF(A3>=93,”A”,IF(A3>=90,”A-“,IF(A3>=88,”B+”,IF(A3>=83,”B”,

IF(A3>=80,”B-“,IF(A3>=78,”C+”,IF(A3>=73,”C”,IF(A3>=70,”C-“,IF(A3>=68,”D+”,

IF(A3>=63,”D”,IF(A3>=60,”D-“,IF(A3>=59,”F+”,”F”)))))))))))))

The nested if is a long and time consuming formula to create but it’s relatively easy to understand. In the ‘old days’ of Excel (before Excel 2007) only 7 nested ifs were possible but now the limit 64 so this solution works nicely as there are a limited amount of grades.

Speed to Create if the number of grades is limited (i.e. 5 if no +- grades up to possibly 12) copy/pasting the IF and changing the values wouldn’t take that long to create. If we were binning for another reason (not grades) that had 50 groups then the IF method would be too time consuming to create. Better to use a binning table (see Solution 3 below).

Understand/Audit I would have to say that it’s easy to understand. Just read the ifs from left to right. Include a default grade (“F” in this case).

Modify simply start defining at the top (i.e. IF(A3>=98,”A+” ) and keep moving down.

Calculate The ifs, or bins, are limited so calculation speed isn’t a factor.

Error What if someone enters text? It would default to an A+ !! Why? Because every character that you can type has a number. Use the CODE function and reference a value in a cell to see how this works. What can we do? Modify this solution be adding   IF(ISTEXT(A8),””,   immediately to the right of the = . Make sure than your numbers really area numbers and not numbers stored as text!

 

Solution 2  ‘Short & Sweet’ NESTED IF

=IF(A3>=90,”A”,IF(A3>=80,”B”,IF(A3>=70,”C”,IF(A3>=60,”D”,”F”))))&IF(A3>98,”+”,IF(A3<59,””,IF(OR(RIGHT(A3,1)={“1″,”2″,”0″}),”-“,IF(OR(RIGHT(A3,1)={“8″,”9″}),”+”,””))))

I found this shorter nested if formula on David Hager’s blog. David calls it “Short & Sweet” as it’s shorter than the typical nested if and it doesn’t require a mapping table.

Speed to Create If I lost this formula it would definitely take me much longer to re-create. Nested if that also concatenates and uses an array constant! The work of a very skilled data analyst makes this tricky and slow to create.

Understand/Audit It definitely takes more time to understand this formula. Data analysts will be intrigued by this clever formula.

Modify Although it’s challenging to understand and then modify it’s kind of fun to play with. David’s formula is a data lover’s formula.

Calculate We’d need to have 1000s of students to see how fast/slow this is. As this is rare, it’s not a concern.

Error What if someone enters text? Read ‘Error’ in ‘Long NESTED IF’ solution above. Be careful modifying this formula. Buy a coffee for your local data analyst and they’ll help you!

 

Solution 3  LOOKUP/BINNING TABLE

This has been my favorite solution over the years. Once I can explain the approximate match concept my experience is that people love this solution. It’s so easy to modify the bin ranges. Just remember that the numbers need to increase as you move down the column.

Speed to Create It’s super quick to create a mapping table and then use one of these functions: lookup, vlookup or index/match. Don’t forget to use approximate match! This means use TRUE as the last match type and not the more common FALSE (only use TRUE as in this case we are binning!).

Understand/Audit The approximate match concept might initially take time to understand but after that it’s easy.

Modify Changing the grade limits are super easy using this method once the approximate match concept is understood.

Calculate Once again, not really a factor as you would almost never have a huge amount of students (i.e. 200000 +)

Error With two steps (mapping table and then lookup formula) I guess there is a chance that an error can occur.  Mapping table must be ordered correctly (larger numbers as you move down). The rare lookup function is probably the best (or Index/Match combo) as there is no column index number to worry about (if you hard code the column index number in a vlookup then inserting a column in the middle of the table would break the solution).

 

WHICH SOLUTION DO YOU PREFER AND WHY DO YOU PREFER IT ???

 

Related Post

The Dangers of Binning in Excel

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.

There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy playing with my dogs Cali & Fenton, learning Spanish and playing Chess and reading Excel books 🙂

Post a comment