Introduction
The IFS function can overcome the need for Nested IF statements, as you are about to see. Additionally, the CHOOSE function can easily be a substitute for the IFS function in some cases, as you are also about to see.
Nested IF v IFS
In the screenshot above, the idea is that if USA is entered in any of the cells A21:A25, the value 0 will appear in the corresponding row in column B. For UK, the value 5 should appear and for Ghana the value 7 should appear.
The range B21:B25 uses the nested IF function that you can see highlighted in cell B26.
The range D21:D25 uses the IFS function you can see highlighted in cell D27
I would say there is not a lot of difference between these two functions. However, take a look at my income tax example from this page on this Blog: https://excelmaster.co/tax-computation/
In that tax computation example, I use five separate IF functions to calculate someone's income tax: of course, I could create one mega Nested IF formula to do that calculation but that is not best practice:
The following screenshot shows the same tax computation using a single IFS function. I think this option is superior to the five IF statement solution.
IFS v CHOOSE
In the following example, in which we are entering the number of the day of the week in cell L5, I am offering two solutions by which Excel matches day number with day name:
Cell M5 =IFS(L5=1,K5,L5=2,K6,L5=3,K7,L5=4,K8,L5=5,K9,L5=6,K10,L5=7,K11)
Cell N5 =CHOOSE(L5,K5,K6,K7,K8,K9,K10,K11)
In this example, I would say that the CHOOSE function is much easier to write and to understand, even though both options provide exactly the same answer as each other.
There you are Nested IF, IFS and CHOOSE functions with two very simple and one relatively complex example to work with.
There is no file to download given the simple nature of the data we are working with.
Duncan Williamson
5th December 2019
No comments:
Post a Comment