When we start using Excel, a lot of us dabble with it by creating simple formulas. After that, we might perform some more complex calculations using some of Excel’s pre-set functions, and in this context by far the most powerful feature in Excel is the nested IF function.
In this short post, we will first look at the basic IF function, and then explore the more advanced nested IF function.
IF Function
IF is one the most used functions in Excel because it gives users the power to display a certain value depending on whether the data satisfies a particular condition.
The syntax of IF is very simple and is as follows:
=IF(logical_test, [value_if_true], [value_if_false])
- logical_test – Condition that will be evaluated
- [value_if_true] – Value displayed if the condition is true
- [value_if_false] – Value displayed if the condition is false
IF Function Example
To illustrate how IF works, let’s take a simple example as seen in the picture below. The formula we input in C3 is:
=IF(B3=”Hello”,1,2)
When “Hello” is inputted into B3 the result in C3 will always be 1, and if anything else is inputted into B3 the result in C3 will always be 2.
Nested IF Function
Now that we have explored the basics of the IF function, let us turn our attention to the concept of ‘nesting’ and how different formulas can be nested within an IF function.
Nesting simply involves using another formula as one of the variables within the IF function. Nesting is mainly used when you need to make a lot of calculations in order to get an answer, but don’t wish to see the results of each step. Great care should be taken when creating a nested IF function because the conditions will be read by Excel from left to right.
A basic example of a nested function is shown in the formula and picture below:
=YEAR(TODAY())
The TODAY function returns today’s date, and the YEAR function takes that date and outputs the current year.
Nested IF functions can appear even in simple formulas, so expect to find them regularly in complex ones.
Nested IF Function Examples
The examples below showcase the power and agility of the nested IF function:
1. Nested IF for Grades
One of the main problems that nested IF solves is the grading problem.
Imagine there is a group of students who have taken part in an exam. You have assigned the scores pictured below in column B and need to assign each student with a letter grade. This problem can be solved automatically by inputting the score for the first student into the nested IF function below and then dragging down to complete column C:
=IF(B2<55,”F”, IF(B2<65,”D”,IF(B2<80,”C”,IF(B2<90,”B”,”A”))))
The grades in column C are produced by the formula above. For example, Nathan Drake has a score of 85 which gives him the letter grade B. This is in line with the rule that if B4<90, then a ‘B’ should be displayed. Dwyane Wade on the other hand scored 56 which gives him letter grade D, which is in line with the rule that if B3<65, then a ‘D’ should be displayed.
2. Nested IF with VLOOKUP
Another key problem that nested IF solves is to check whether a product is currently in stock.
Imagine there is a list of vehicles (pictured below) and one wants to check if they are in stock. This is solved by inputting the formula below into cell F2, and writing the name of the car into cell F1:
=IF(VLOOKUP(F1,A2:B8,2,0)=0,”No”,”Yes”)
As it can be seen, the selected vehicle is not in stock and as a result the answer of the query “In Stock?” is “No”. If another vehicle that is in stock had been selected, the answer to the query would be “Yes”.
Final thoughts
The Nested IF Function is one of the most powerful tools in Excel’s arsenal.
The best way to learn how to use this function is to experiment with it as much as possible. Once you become proficient in using the Nested IF Function, you should be able to solve most of your issues in Excel with this technique.
Loredan Emini graduated from Rochester Institute of Technology in Economics & Management. He currently works as a Power BI specialist for a German company, and as a lecturer of Power BI.
Image: Pixabay