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, especially Nested IF, many spreadsheet users find the function very useful as it is very intuitive to construct formulas that have logical constraints.
While the IF() function is intuitive while building, often formulas built with the if function -- especially nested if -- start 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:
One of the formulae I reviewed looked 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 $C$29 at the end whereas the value-if-false part has $C$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.
This formula was applied in several cells across multiple rows. That means we had such a long formula in every cell in which it was used. However, if you notice, some part of the formula is an absolute reference. And that means, their product is going to give the same result irrespective of where the formula is pasted.
So, how about we put the absolute reference calculation in a separate cell and link everything to it?
So, we will have one extra cell, say B35, that have one part of the calculation:
B35 = $B$17*$B$21*(1-$B$34) And the rest of the formula would read as follows:
=IF(E22>P22,$C$29,$C$28)*$B$35^(E22-1)As you can see, this approach has made the formula very easy for us to read and understand. And this principle not just makes us shrink the IF() function but you can reduce the size of all the functions by ensuring that separating the concern and by ensuring that you do not repeat any calculation more than once.
Further,
by avoiding repetition of calculation (i.e. by creating a helper cell), we also avoid putting unnecessary load on the CPU to do the same calculation multiple times. Thus, your files also become faster.
Having said that, if you are working with dynamic arrays, having a helper cell may actually be slightly inefficient. But that is a discussion for another day.