How to Write a Nested IF Statement in Excel

by Matthew Kuo on December 31, 2015

in Excel, Formula Writing

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon
 

Nested IF 01

Writing a Nested IF Statement is widely considered a rite of passage for beginning Excel users who are looking to become more advanced.  The first time I used this technique was during my new employee training, right before I became a business analyst in management consulting.  The overall concept of writing a Nested IF is actually fairly simple; it’s just that process of doing so is highly prone to making mistakes, which can make learning it somewhat difficult.

Click here to download the MBA Excel Nested IF example file

What is a Nested IF

A Nested IF statement is defined as an Excel formula with multiple IF conditions.  It’s called “nested” because you’re basically  putting an IF Statement inside another IF Statement and possibly repeating that process multiple times.  Below is a visualization of how a simple Nested IF works.  We have two IF Statements, one highlighted in Red and one highlighted in Green.

Nested IF 02

The trick to making the Nested IF work is that the false or “ELSE” condition of the first IF Statement is another entire IF Statement.  The Green IF Statement is “nested” inside the Red IF Statement.

Two Primary Use Cases

There are two main reasons you would use a Nested IF:

  • To assign a set of numbers to different categories
  • To assign a set of numbers to histogram bins

Both are very similar in terms of execution.  For our example, we’ll be using the Nested IF Statement to assign categories because it is simpler.

Keys to Properly Writing a Nested IF Formula

Before we get started with the actual exercise, there are a couple things you should keep in mind to make sure you don’t make a mistake during the process.

  • Be careful about mixing up your cell references; you’ll go back and forth frequently between selecting your data set cell and your assignment cell
  • Use Excel’s built in reference highlighting to visually check your work
  • Count your parentheses, to make sure you have the appropriate number in there; before hitting enter to finish the formula, check that for every open parenthesis you should have a closing parenthesis
  • Remember to reference lock; while I’ve included it as a step in the overall process, it’s still very easy to forget

Step 1: Define your categories

The first step of writing any Nested IF Statement is to define your goal.  In the following example, our goal is to assign each number in our data set to one of three categories:

  • Low
  • Medium
  • High

Based on the following criteria:

  • Low is from 0 to less than 20
  • Medium is from 20 to less than 40
  • High is any value 40 or greater

For simplification purposes we’ll assume that our data set does not contain negative numbers.

The simplest way to complete this process is to put all of your categories and criteria in a table.  See below for an example based on the conditions we’ve identified:

Nested IF 03

Step 2:  Load your Data Set

Load your data into a vertical column and add a field next to it to assign it to the proper category.

Nested IF 04

Step 3:  Decide on your approach

For any Nested IF Statement, there will be multiple ways to write the actual formula.

For our specific scenario, we need to decide whether we’ll start our category assignment by addressing the bottom category or the top category first.  The approach you choose shouldn’t make a difference; you should just make sure that the formula you end up writing covers all potential conditions and scenarios.

We’ll start from the lowest category just because it is slightly more intuitive to move in that direction (from bottom to top).

Step 4:  Start your IF Statement

The syntax for the IF Statement is as follows:

= IF ( logical_test , [ value_if_true ] , [ value_if_false ] )

The syntax for the Nested IF Statement we plan to write is as follows:

= IF ( logical_test for Category 1 , Category 1 , IF ( logical_test for Category 2 , Category 2 , Category 3  ) )

Notice that the [ value_if_false ] condition for the first category is the entire logical test for the second category.  If the logical test for the second category fails, the third category is selected without using another if statement.

The last logical test of a Nested IF does not necessarily need an IF Statement, however, you would have had to write one if your third category had a defined boundary.  For example, if the high category is 40 – 60 only and you would need to exclude values over 60 from the high category, and you’d do that by using another IF Statement.

For our scenario, because our third category it is 40 and up, and because we have no negative numbers in our data set, you can assume all numbers failing logical_test for Category 1 and Category 2 will therefore be in Category 3.

We’ll have two IF Statements whose results are used to assign values to one of three categories.

To begin, we start out by writing our first IF statement for the first cell we want to evaluate:

Nested IF 05

Step 5:  Write your first logical test for Category 1 and it’s corresponding True condition (which is the Category 1 label)

Our first logical test is just to compare your data set value to the first category, to see if it should be assigned to that category.  Therefore, we basically want to see if the number 33 is less than 20.

Nested IF 06

Note that after selecting our first true condition, we do NOT close off the formula with a parenthesis.  This is because your second logical test IS the [ value_if_false ] condition for your first logical test.

Step 6:  For the False Condition of your first logical test, write your second logical test for Category 2 and it’s corresponding True condition (which is the Category 2 label)

Note that when writing the second logical test, you need to go back to your original reference value, but start comparing it to the second category.

Nested IF 07

Step 7:  For the False Condition of your second logical test, enter the value for Category 3

As mentioned before, we don’t have a to write another IF Statement because we know another that fails the first two logical tests will fall into Category 3.  Therefore, just input Category 3.

Nested IF 08

Step 8:  Close out both the logical test 1 and logical test 2 by placing two parenthesis at the end of the formula

Nested IF 09

Step 9:  Reference lock the appropriate values

You’re not done editing your formula yet.  If you’re writing a Nested IF Statement, you’re likely going to want to copy it to multiple cells.  To make that process easier, you have to remember to reference lock the appropriate cells before copying your formula down.

To determine the cells you need to reference lock, just think about which cells you need to float (change every time you move to a different value), and which cells you need to stay the same for every formula.

In the example below, the value we need to float is C3, or the data set value, because we want each one of these values to be assessed in their individual formula.  Therefore, for all instances of C3, we do NOT reference lock.

For the values we need to lock, it’s basically everything related to the categories and category thresholds we’ve identified (everything in the table to the right).  If any of these values were to change as we copied the Nested IF down, the formula would not work properly.

Therefore, go ahead and reference lock all cells related to the category table to the right, while leaving your data cell value unlocked.  You can reference lock a cell by clicking into the formula, clicking on the cell reference you want to reference lock, and hitting the “F4” key.

Nested IF 10

Step 10:  Copy your formula down to apply it to all data sets

This can be done by double clicking the lower right hand corner of the cell.  Again, make sure you’ve completed Step 7 before moving on to this final step.

Each value in our data set now has been assigned to a category of low, medium, or high and your Nested IF Statement is complete.

Nested IF 11

Click here to download the MBA Excel Nested IF example file

What Excel Does

Excel’s steps are pretty straightforward when it comes to a Nested IF Statement.  Excel just follows the logic you’ve setup in your IF Statements to assign each of your data set values to a category.

  • If your data set value is less than 20, it will be assigned to the “Low” category.
  • If your data set value is less than 40, it will be assigned to the “Medium” category.
  • If your data set values meets neither of these conditions, it will be assigned to the “High” category.

Flaws of Using a Nested IF

If this was the first time you ever wrote a Nested IF Statement, you can tell that the process is tricky and prone to making errors.    And it’s important to keep in mind that we went through one of the simplest examples for writing a Nested IF formula.  See the image below for an example of a more complex Nested IF Statement:

Nested IF 12

The key problems with wring a Nested IF Statement are:

  • It’s difficult to write – the process of clicking back and forth between your reference cell and your condition cells make writing a Nested IF Statement prone to error
  • It’s difficult to audit and check for errors
  • It’s not scalable – leveraging our previous example, every time that I need to add a new category to my assessment, I’ll going to need to update the formula; if I have a hundred categories, then I’ll likely never finish writing the Nested If Statement

Alternatives to Writing a Nested IF Statement

While the Nested IF approach is one of the most common and popular ways to assign categories or histogram bins, there are two methods that are generally regarded as better approaches.  While they are still somewhat prone to error, they are much more scalable and easier to audit for mistakes.  The two alternatives are:

Conclusion

Learning how to write a Nested IF was a very important Excel milestone.  Despite my problems with the formula, writing the Nested IF teaches you how logical structuring (basically, writing IF Statements) works in Excel.  Having a strong level of comfort around logical structuring is critically important to Excel proficiency.  Therefore, if you want to get good at Excel, you should learn how to write Nested IF Statements regardless of which approach becomes your preference.

Click here to download the MBA Excel Nested IF example file

{ 1 comment… read it below or add one }

Alfred January 5, 2016 at 4:09 am

This has been of tremendous assistance. Thanks for this this new knowledge!

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post: