- BY Kevin Lehrbass
- POSTED IN Efficiency, Errors
- WITH 9 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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???**

**Related Post**

**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.

** 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 🙂

The short n sweet formula is so wonderful!!

Yes, it really is an interesting solution 🙂 Thanks for reading my post Jonty !

I am using this as an exercise for my students .. thanks!

Hi Alex,

I’m glad you found my post helpful. Are you an Excel instructor?

Cheers,

Kevin

Thank you very much kevin, because today my stuents were very happy, they even thought it was a magic fomular.

Hi Abduljalal,

I’m glad that your students enjoyed the formula! And thanks for visiting my blog!

Cheers,

Kevin

Thank you so much. Now I will rock my automation practical tommorrow

I copy Solution 1 Long NESTED and paste and fail to create result.

Hi Calvin,

Is the formula referring to the correct cell? Is the value in the cell a number?

Thanks for visiting my blog Calvin.

Kevin