Nested ‘IF’ rethought: Here’s IFS
The ‘IFS’ function simply expands on the idea of nested ‘IF’ functions.
However, it simplifies things by allowing us to place a number of logical tests and subsequent value if that logical test is true without the additional need to supply a value if the test is false.
Let’s just take a look at the syntax to clarify:
‘=IFS(Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127])’
Notice that we no longer need to nest a new ‘IF’ function into the ‘value_if_false’ argument of the previous ‘IF’ in order to add a new logical test.
With the ‘IFS’ function we can just add as many tests and values if true arguments up to a maximum of 127. This adds an incredible amount of efficiency and removes much of the complexity that comes with nesting ‘IF’ functions.
So let’s take a look at what an ‘IFS’ function would look like if we use it in place of the nested ‘IF’ function in our previous example where we tested for our list of 4 colors.
While our new formula may not seem any shorter in length, it is clear by examination that it is far simpler and easy to read.
One very important thing to realize about the ‘IFS’ function is that it has no ‘value_if_false’ argument. This means that we have to compensate by creating a ‘Something is True’/’Value if True’ pair that achieves the same goal.
Notice in the previous figure where our last ‘Something is True’/’Value if True’ pair that we have used ‘A7<>”Purple”’ as our last ‘Something is True’ argument.
The ‘IFS’ function essentially goes through the list of supplied ‘Something is True’/’Value if True’ argument pairs until it finds a [true] match.
This is why we have to develop a pair that creates a solution for those cases in which our test value will not match the previous ‘Something is True’/’Value if True’ pairs.
In our example, we simply used a statement that said if the value in A7 did not equal ‘Purple’, then return the value ‘Other’. Note we only need to test for one color of the list at this point because it is only relevant if the value in ‘A7’ had already failed the previous tests.
Let’s take a look at another example using ‘IFS’. In this scenario, we have sales figures that we want to test and then apply a bonus amount accordingly. We have a progressive bonus schedule that shows the payout based on a sales quota amount.
So our final formula will test our sales amounts against five different quota amounts and return the appropriate bonus.
We could certainly nest ‘IF’ functions to achieve our goal and it could look something like this:
Note that there is a ‘$’ in some of the cell references and for anyone unfamiliar with this, it is simply a method to lock either the row, column, or both, of the cell reference so they do not change as we copy the formula to all rows for the test data.
But the main thing to notice with this formula is the number of closing parentheses at the end of it.
Clearly, as we add more ‘IF’ functions to the existing formula, things get very complicated and difficult to manage.
Let’s take a look at how to achieve the exact same thing using the ‘IFS’ function as an alternative to the nested ‘IF’ approach.
Notice again like in our previous example how the formula is set up to test each quota level until it progresses the last argument pair.
In this case, the sales amount will cause the formula to return the ‘Value if True’ argument for the last pair that it matches as true. This example may seem a bit confusing as you review the formula, so let’s walk through it.
Notice that the first argument pair tests to see if the value in cell A2 is less than the value in E2, which is $250.
If this evaluates to true, the formula should return a value of ‘0’.
This is because unless the sales value is at least $250, there is no bonus paid out.
The next step of the formula tests to see if the value in A2 is less than the value shown in E3, which is $500.
If it is less than $500 but it did not test true as less than $250, the formula is designed to return the value in cell F2, which is $25.
This simply means that if the sales amount is greater than $250 but less than $500, the bonus should be $25.
The next few argument pairs work in the same fashion until we get to the final pair.
If our sales amount has not evaluated true for all argument pairs up to this point, that leaves us with one last test.
Since $1,250 this is our last quota value to test against, we do not have a subsequent value to evaluate the sales amount as being less than.
Therefore, if the sales amount has not been less than any of the quota amounts up to the value of $1,250 in cell E5, it must be greater than that value and our task is to tell our ‘IFS’ function how to evaluate that.
Now we simply ask the formula to test if the sales amount value is greater than E6 and to return the bonus value in F6.
See in the next figure that both the ‘IF’ and ‘IFS’ formulas evaluate to the same results.
Rounding up on nesting…
Obviously, the ‘IFS’ function provides a much clearer and more efficient alternative to nested ‘IF’ functions.
However, for anybody who has used nested ‘IF’ functions in the past, it may take some getting used to the syntax due to the absence of any ‘value_if_false’ argument. There is little doubt, however, that the function offers a more streamlined approach to the same problems providing a more elegant solution.