- This topic has 3 replies, 2 voices, and was last updated 4 years, 2 months ago by
.
-
Posts
-
Microsoft kept their launch of Office 2019 a low-key affair, preferring to steer us towards Office 365, their subscription service. When I looked through the list of new Excel features recently, one of them had me thinking: why didn’t they do that years ago? As is often the case, it’s the simplest enhancements that save the most time.
IFS() is a new and improved conditional function. The existing IF() function takes three arguments: the condition to test, the result if the condition is true and the result if the condition is false. For example:
=IF(A1<10,”Low”,”High”)
will return the text “Low” if the number in cell A1 is lower than 10, otherwise it returns the text “High”. But what if you want to further test for other ranges higher than 10? You can nest IF() statements like this:
=IF(A1<10,”Low”,IF(A1<20,”High”,”Very high”))
This would return “High” for values of cell A1 between 10 and 19 and “Very high” for values of 20 and over. It works fine, but nested IF() functions become difficult to read and maintain. Even when you manage to write the formula correctly, you have to count through the brackets to work out how many you need to close off the function without an error.
IFS() simplifies linked conditional tests. It takes arguments in pairs: if the first argument is true, the second argument is returned; if the third argument is true, the forth argument is returned, and so on. So, in my example above, I would use:
IFS(A1<10,”Low”,A1<20,”High”,TRUE,”Very high”)
The first pair of arguments (in red) tests for A1 being less than 10. The second pair (in green) tests for less than 20. The third pair (in blue) is a catch-all that’s evaluated if no other conditions are matched.
Here’s another example:
This shows both IF() and IFS() used for the same purpose: to convert the number entered in cell C11 into one of the days of the week listed in the box. IFS() is simpler and cleaner.
You’re welcome to download this workbook. Try IFS() for yourself and share your thoughts. Of course, IFS() will only work if you use Excel 2019.
Tom Howard,
Westbay Engineers Ltd.Thanks for that. I agree it’s a function that makes life easier, but I don’t like the way they handled the default catch all option. You have to use the final pair of arguments. By setting the penultimate argument to “true”, the final argument is always returned if no other tests passed.
It’s logical but feels clumsy. Why don’t they check whether there are an even or odd number of parameters. If it’s odd, then use the final argument as the catch-all with no unnecessary final test. In fact, this would also make it a direct replacement for IF with only one test covered with three arguments. You could add further tests and results by adding arguments 3 and 4, then 5 and 6 etc.
SWITCH is a similar function that was added last year. The first parameter is the expression to evaluate. If it equals the second parameter, the third is returned. If it equals the fourth parameter, the fifth is returned and so it continues in pairs. The final parameter is the default if there were no other matches. In Tom’s example it could work like this:
If cell C11 equals 1, return cell B9 but…
If cell C11 equals 2, return cell B10 otherwise…
Return the text “None”Which you’d enter as:
=SWITCH(C11,1,B9,2,B10,”None”)
Richard,
That’s true. SWITCH() is a useful function. It’s probably easier to use than IFS() but has the following limitations:
- With SWITCH(), there can only be one expression being evaluated and you put it in the first argument. In my example, it would be C11. With IFS(), all of the conditons being tested can be unrelated, so:
If C11 equals 1, return this, otherwise
If G19 equals 2, return that
- The logical condition can only be = (equals). You cannot test > (greater than), < (less than) etc. Seems a silly omission to me.
I’ll put up a list of all the new functions introduced in Excel 2019 in another thread.
Tom Howard.
- With SWITCH(), there can only be one expression being evaluated and you put it in the first argument. In my example, it would be C11. With IFS(), all of the conditons being tested can be unrelated, so:
- The forum ‘Excel Boardroom’ is closed to new topics and replies.
