Here’s the scenario: We have a review system where questions are answered with a 1 to 5 rating system; 1 being the worst and 5 being the best.
We want to see words in place of the numbers, so we write a nested IF formula like the following:
=IF(A1=5, "Excellent", IF(A1=4, "Good", IF(A1=3, "Neutral", IF(A1=2, "Fair", IF(A1=1, "Poor", "N/A") ) ) ) )
Does that make your head hurt?
Now let’s see how the IFS function handles the same task.
=IFS(A1=5, "Excellent", A1=4, "Good", A1=3, "Neutral", A1=2, "Fair", A1=1, "Poor", TRUE, "N/A")
I know what you’re thinking, “That doesn’t look much better at all!”
Look closely. The first version has 5 IF functions nested within one another. That’s 5 sets of parentheses to wrestle with.
The second version has but a single IFS function; 1 set of parentheses.
With nested IFs, you can have up to 64 levels of IFs within one another. Can you imagine keeping track of all those parentheses?
With the IFS function, you can have up to 127 decisions in a single function.
When you’re working with nested formulas, Excel will color-pair the parentheses to help you associate the open/close pairs. Because there are only 8 color pairs, Excel will repeat the colors for the 9th and beyond levels.
The PRO TIP is that the BLACK pair is the only color pair not to be repeated. This means, when you are closing things off at the end, you can just add close parentheses until you see the BLACK parentheses. That’s where you need to stop.