The IF function is one of the most used functions in Excel as it helps us test whether a condition is true or false. If the condition is true, the function will perform a certain action, but if the condition is false then the function will perform a different action.
This is how the IF function allows us to evaluate the result of a logical test and take an action based on the result. The following image illustrates the operation of the SI function.
The diagram above makes it clear that the IF function it will evaluate at most one logical test and we can execute up to two possible actions.
An example that can be perfectly served by the IF function is the following. Suppose we have a list of ages and in a new column we must place the legend “Minor” in case the age of the person is less than 18 years old, but if the age is equal to or greater than 18, then we must place the legend “Of legal age”.
The solution to our problem will be to display two different legends, that is, we need to execute two different actions based on the logical proof of age. This problem is easily solved by using the IF function as follows:
=SI(A2 < 18, "Menor de edad", "Mayor de edad")
The IF function will display the first message only if the logic test (A2 <18) is true. If that logical test is false, then the second message will be displayed. Look at the result of applying this function to our sample data:
The IF function nested in Excel
We solved the previous problem easily with the IF function because the final result would be only two actions, but now imagine that we need to display the legend “Third age” when the person is 65 years or older. This leaves us with the problem with the following rules:
- Under 18 years: “Minor”
- Over 18 years old and under 65 years old: “Of legal age”
- Greater than or equal to 65 years: “Third age”
Now we have three possible actions, and the IF function will not be able to solve this problem by itself, so we need to resort to the nested IF function which will allow us to solve any situation in which we need to evaluate more than one logical test and execute more than two actions.
The following image shows the operation of the nested IF function. Note that the key is that, instead of executing a second action, the first IF function includes a second IF function so that between both functions they can execute a maximum of three actions.
The IF function nested in Excel increases the flexibility of the function by expanding the number of possible results to be tested as well as the actions that we can execute. For our example, the nested IF function that will properly solve the problem will be the following:
=SI(A2 < 18, "Menor de edad", SI(A2 < 65, "Mayor de edad", "Tercera edad"))
Pay special attention to the third argument of the first IF function, which instead of being an action, has become another IF function that in turn will have the possibility of executing two other actions.
A very common mistake in creating a formula that uses the nested IF function is trying to use the first IF function with four arguments, which will cause an error. The first IF function must “renounce” one of its actions to give us the possibility to use another IF function.
Excel will do the analysis of the above formula as follows. Starting with the first YES function, it will be evaluated if the age is less than 18, if this condition is met, the legend “Minor” will be displayed and everything will end there. On the contrary, if the first condition is not fulfilled, it means that the age is greater than or equal to 18 and the second IF function will be executed.
In the second logical test we will test if the age is less than 65, if it is true, the legend “Of legal age” will be printed, but if the logical test is false, it will mean that the age is greater than or equal to 65 and therefore Therefore the label “Seniors” will be printed. Look at the result of applying this formula to our sample data.
Another example of a nested IF function
More than another example of the nested IF function in Excel, I want to highlight the fact that not all the formulas created will be the same even for the same problem since everything will depend on the logic you decide to implement. For example, we can solve the same exercise in the previous section with the following formula:
=SI(A11 >= 65, "Tercera edad", SI(A11 >= 18, "Mayor de edad", "Menor de edad"))
In this formula, the logic used is completely inverse to that of our previous formula, that is, the first IF function will evaluate if the age is greater than or equal to 65 to print the legend “Third age”, otherwise the logical test will be performed to know if it is greater or equal to 18 years.
In the following image you can see that I have placed this formula next to our first example and the result is exactly the same.
In this way we can see that there are different ways to solve the problem and most likely the resulting formula will be different for each Excel user. So, if you are a new user to Excel, I highly recommend that you spend enough time deciding which logical tests to use in each IF function. I also recommend that you solve the problem on paper before trying to write the formula directly in Excel.
On more than one occasion I have seen that the errors presented in a nested IF function come from a bad implementation of the logic used. In addition to this, the little experience in the use of the comparison operators in Excel will cause several problems when properly constructing the logical tests used by the SI function.
If you think it is convenient to refresh your knowledge about these operators a bit, I recommend you consult the video tutorial Types of operators in Excel paying special attention to the comparison operators section.
Multiple nested IF functions in Excel
In our previous examples I have used only two nested IF functions, but Excel allows us to nest many more functions. Originally only up to 8 nested functions were supported but as of Excel 2010, it is possible to nest up to 64 SI functions.
Although currently the maximum is 64 nested functions, you will not reach even half of that limit when you begin to have difficulty understanding the logic used. However, it is important that you know this limitation in Excel and remember that it is impossible to exceed this limit. I will repeat it again because it is a very frequently asked question: remember that it is impossible to make Excel exceed the limit of nested functions set by the manufacturer.
However, in the meantime and do not exceed the limit established by Excel, you will be able to nest as many functions as you need and all you have to do is replace an action of the IF function with another IF function. To make this clear, let’s do one last example where we have a group of scores from 100 to 999 that we will evaluate as follows:
- 100 to 599: Bad
- 600 to 749: Average
- 750 to 899: Good
- 900 to 999: Excellent
An alternative workaround for this problem is the following nested IF function:
In this case I have three IF functions nested because the number of actions I need to execute is four. So generally speaking, the number of IF functions you will use in your formula will be one less than the number of actions you need to execute.
Also note that all IF functions are “nesting” another IF function as one of their arguments and only the most nested IF function will have its two actions because there will no longer be another IF function to nest. Look at the result of our formula:
The reality is that practice is the best way to perfect your use of the nested IF function in Excel. So, I suggest you start with the examples published in this article and then continue practicing with your own exercises.