Some of the first functions that a newly-titled “power user” learn are IF and VLOOKUP. The IF function provides the user the ability to ask a question and perform action “A” for one answer and action “B” for another answer. The catch is that any question posed must be answerable as either “true” or “false”.
A simple example would be something like, “Are the contents of cell A1 greater than the contents of cell B1?” If the answer is “yes”, calculate a 10% bonus; if not, then no bonus is offered. This IF (sometimes referred to as a “simple IF”) has the built-in limitation of only being able to perform a single evaluation. What does one do if several evaluations need to take place?
Enter the Nested IF
The nested IF is simply an IF inside of an IF, which could then be inside another IF. Excel’s IF has a limit of 64 nested levels. This provides the user with up to 65 possible actions to take.
(SIDE NOTE: It is often suggested that if you are writing more than 4 or 5 levels in a nested IF, you should really think about using a different function to accomplish your task, such as VLOOKUP.)
Let’s take the following example of determining letter grades based on test scores. The below table shows the ranges that grades fall into.
Assuming we name the cell containing the final score “score”, the formula to calculate the letter grade would look as follows:
This formula is only determining five (5) possible outcomes, yet it suffers from several problems.
- Each function asks a variation on the same question about the same source data
- The length of the formula is becoming excessive
- The opportunity to make a typo increases exponentially (or geometrically; whatever. It’s big!) the longer the formula becomes.
- The formula becomes increasingly difficult to keep track of parenthetical pairing.
EXCEL 2016 and New Functions Save the Day
Excel 2016 is equipped with two new functions called IFS and SWITCH that help mitigate all of the above problems associated with multi-level nested IF statements.
Using our current example of number grades to letter grades, we can greatly reduce the length and repetition of the original nested IF by using a new function called IFS. The new IFS function strips out the need for creating a new IF statement for each new evaluation. Below is the same formula but accomplished with an IFS function.
The new IFS function is not doing anything more or less than the original nested IF, but it performs the task more elegantly due to the removal of the redundant IF functions.
Notice there is only a SINGLE set of parentheses instead of four, or 64 in the worst case scenario.
(SIDE NOTE: The IFS function supports 127 separate evaluations, compared to 64 evaluations when nesting older IF function.)
Let’s Flip the Question
Suppose you know what the letter grades are but you need to discover the test score ranges.
The function to use in this scenario is called SWITCH. The SWITCH function is similar to the IFS function except that it simply looks for an instance of data in a list and makes a corresponding offer of new data.
Think of it as a VLOOKUP with the table built right into the formula.
In the SWITCH function, a value (or expression) is compared to items in a list. The first matching item in the list will then return a result. Because SWITCH halts the evaluation when it encounters a matching value, it is recommended that the most likely matching item(s) be placed first in the list. This will reduce the amount of unnecessary comparisons.
Another Use for SWITCH
How about this example?
You have a spreadsheet with a column of dates and you want to determine if a date is occurring today, yesterday, tomorrow, or in the near future or near past. We could write a formula like the following to calculate the number of days between the defined day and today.
This would produce the following table.
By nesting the above function inside of a SWITCH function, we can convert those numbers into words.
=SWITCH(DAYS(TODAY(),A1),0,”Today”,1,”Yesterday”,-1,”Tomorrow”, “Out of Range”)
Splash a bit of Conditional Formatting color on the list and you now have something like the following.
Sort the list by date in descending order and you have the result below.
Let’s Pair them Up for a Super Function
If we nest the SWITCH function inside the IFS function, we can make it a bit more sophisticated.
The SWITCH function is simply looking for matching items. Suppose we want to introduce a bit of comparison logic into the mix. Let’s say that if an item is greater than one (1) day in the future it is considered “Pending”, and if it is greater than one (1) day in the past it is considered “Processed”. The days between those points in time are where we want the “Yesterday/Today/Tomorrow” logic.
The IFS function will determine the “Pending/Processed” status while the SWITCH will determine the “Yesterday/Today/Tomorrow” status