How to shrink the IF function
Viswanathan M B
Thought leader in finance | Consulting and Training| YouTube.com/c/ProfectusLearning
Today, I was reviewing one of the financial models (in MS Excel) that used a lot of the IF construct. Although I generally recommend users to avoid the IF construct, many spreadsheet users find the function very useful as it is very intuitive to construct formula that have logical constraints.
While the if function is intuitive while building, no doubt, it makes the formula too lengthy running into several lines. This makes it almost a herculean task for anyone else to decipher what the formula does. Even the same user may struggle to understand if they look at their own work after a few days.
However, here is the tip for those of you who like your IF function but is annoyed at the size. The solution is what the programmers call as the D.R.Y (do not repeat yourself) principle. Let me illustrate that with an example:
The formula I came across today read something like this:
=IF(E22>P22,$B$17*$B$21*(1-$B$34)^(E22-1)*$C$29,$B$17*$B$21*(1-$B$34)^(E22-1)*$C$28)
As you can see the formula ran into multiple lines. But if you closely notice, there is only one difference between the two expressions sitting inside the IF function. The value-if-true has $B$29 at the end whereas the value-if-false part has $B$28 at the end. This formula could have, therefore, been made leaner by taking the constant portions out.
=IF(E22>P22,$C$29,$C$28)*$B$17*$B$21*(1-$B$34)^(E22-1)
As you can see, the formula is fairly leaner now. Further, if you notice, certain portion of the function are absolute reference. Which means, their product is going to give the same result irrespective of where the formula is pasted. So, how about we put the product in a separate cell, say B35 and link everything to it?
So B35 = $B$17*$B$21*(1-$B$34) and the IF function could've become like this:
=IF(E22>P22,$C$29,$C$28)*$B$35^(E22-1)
I would even recommend that underlined part of the formula is kept in a separate cell and then we link back to it. But that is another principle reserved for another day.
Senior Financial Planning & Analysis Consultant
7 å¹´With all due respect, I can only agree with the first portion of the argument. The if function is misplaced at the beginning, as you suggest, and should be placed before the differentiating cells, c28 and c29, as you have done. Where I have an issue is with the intermediate reference summarizing the first part of the equation, the constant. While that is necessary and even sometimes inevitable, what winds up happening in large, complex financial models is that you wind up playing hopscotch through these cells to get back to the source information. Better to have a longer if statement, imo, that includes as much of the direct information as possible.
Research, Analysing, Data Analysing, Risk Management
7 å¹´Viswanatan Anand! Chess World Championship !
Senior Data Reporting Analyst
7 å¹´It would be easier if the result of the other 4 references was derived into a single cell reference first, then used in an IF formula with $C$29,$C$28. Fashioning expressions is not unlike Algebra, in that we group an expression into a single result before substituting into another expression, otherwise, we will get a kilometric expression such as what we have above. You can always trace the result back to the dependent expressions anyway. And depending on your circumstances, there are other helpful functions such as Median and Choose, as well as the traditional vlookup, which allow you to cover multiple conditions beyond what IF is capable of.
Retired at Stanford University
7 å¹´For what it's worth, single cell formulas usually calculate faster according to Microsoft.
Associate | Infrastructure at DC Advisory
7 å¹´John Corr