When I review my Excel formulas from many years ago, one of the types of formulas that I’m most embarrassed about today is the terrible way that I structured my nested IF formulas.
I’d start off with a simple formula like this:
=IF(A=B,C,D)
This says, of course, “If A equals B, return C, otherwise return D.”
So far, no problem.
But then I’d realize that in some cases, I might need to return something other than C or D. So I’d modify the formula by adding other IFs, and modify it again with more IFs, and so on—until I had a formula like this:
=IF(A=B,IF(E=F,N,G),IF(H=I,J,IF(K<>L,M,IF(P=S,D,T))))
And here, of course, my “A”, “B”, and so on were actual calculations, using other worksheet functions, cell references, and range names—which caused the formulas to get so long that they’d wrap at least once in my formula bar.
What a mess!
But somewhere along the line, I finally realized that I should structure all my IF functions like this:
=IF(LogicalTest=TRUE, ReturnValue, DefaultValue)
(Note: You don’t need to add “=TRUE” in your actual formula. Your LogicalTest already is returning TRUE or FALSE.)
And then, if I wanted to add more logical tests, I’d always add the test to my DefaultValue argument, never to the ReturnValue argument. By taking this approach, my tested IFs all had a pattern like this:
=IF(Test1,ReturnValue1,IF(Test2,ReturnValue2,IF(Test3,ReturnValue3,DefaultValue)))
Then one day, I noticed that Excel no longer complained about spaces in formulas. So now, I make nested formulas like this much easier to read by adding two or three spaces in front of each IF function, like this:
=IF(Test1,ReturnValue1, IF(Test2,ReturnValue2, IF(Test3,ReturnValue3, DefaultValue)))
So, for example, you might have a formula like this:
=IF(A=B,11, IF(C<>D,22, IF(E<F,33, 99)))
Not only does this approach make my IF logic more clear, it makes it easy for me to count the number of my IF functions. And that means that it’s now easy to know how many closing parentheses I need to add to the right side of my formulas—not counting the number of parentheses needed by other functions used within my IF formulas.
With this approach, my logical tests can be as complicated as I need them to be. That is, they can be calculated by using logical tests like this:
- A=B
- A<>B
- AND(A=B, C<>D, E>F)
- OR(A<>B, C=D, E<F, G>H)
- ISNA(whatever)
- ISERR(whatever)
- Etc.
But because I always structure my nested IF functions like this…
=IF(LogicalTest, ReturnValue, DefaultValue)
…I always can follow the logic of what my nested IF functions are trying to do.
Excel’s IFS Function
In Excel 2019 and 365, Microsoft introduced the IFS function. Here’s its syntax:
=IFS(LogicalTest1,ReturnValue1, …,…, TRUE,DefaultValue)
That is, the IFS function uses the same logic that my nested IF formulas now use, except that each logical test and its result are grouped as pairs, separated by commas.
However, this function won’t clarify your worksheet formulas very much unless you separate each LogicalTest,ReturnValue pairs with two or three spaces, as I illustrate in the nested-IF syntax formula above.
And finally, when you use an IFS function, remember to end its LogicalTest,ReturnValue pairs with a TRUE constant, followed by the default value you want your formula to return. If you don’t follow that practice, the IFS function will return #N/A whenever all of its tests fail.
