Abstract
Transcript
In this video we'll look at how to simplify some formulas we created in a previous video by replacing IF statements with the MIN function and a bit of Boolean logic.
Make sure you watch the first video if you haven't already.
In the example we have formulas that calculate a company match for an employer-sponsored retirement plan in two tiers.
The calculations for both tiers use one or more IF statements, and the second formula is a bit complicated.
Let's look at how we can simplify these formulas.
=IF(C5<=4%,C5*B5,4%*B5)
For Tier 1, the company match is capped at 4%. If the deferral is less than or equal to 4%, we can simply use it as is and multiply C5 by B5, but when the deferral is greater than 4%, we simply multiply 4% by B5.
First, we can simplify things a bit by just having the IF function figure out the percent. Then multiply the result by B5.
=IF(C5<=4%,C5,4%)*B5
It's always a good idea to remove duplication in a formula when possible.
But we can also remove the IF function completely by using the MIN function instead.
=MIN(C5,4%)*B5
Essentially, the way this works is that we take the smaller of C5 or 4%, then multiply by B5. No need for IF.
For Tier 2 we have a more complicated formula:
=IF(C5<=4%,0,IF(C5<=6%,(C5-4%)*B5,2%*B5))*50%
Now in the outer IF, we check the deferral, and if it's less than 4%, we're done. This means the entire match is handled in Tier 1, so Tier 2 is simply zero.
However, if the deferral is greater than 4%, we use another IF. This IF checks if the deferral is less than or equal to 6%. And if, so, we subtract 4% and multiply by B5. If not, we just use 2% since 2% is the maximum match in Tier 2.
Let's first move B5 out of the IF like we did before.
=IF(C5<=4%,0,IF(C5<=6%,C5-4%,2%)*B5)*50%
Now we can rewrite the inner IF with the MIN function similar to what we did in Tier 1.
=IF(C5>4%,MIN(2%,C5-4%),0%)*B5*50%
In this case, we're taking the smaller of 2% or C5 - 4%, and multiplying the result by B5.
This is a simpler formula, but we can go one step further using Boolean logic.
Boolean logic takes advantage of the fact that in Excel, TRUE evaluates to "1" and FALSE evaluates to "0". Note that the expression C5>4% is a logical expression that returns either TRUE or FALSE.
That means we can remove IF and just multiply the expression times the rest of the formula:
=(C5>4%)*MIN(2%,C5-4%),0%)*50%*B5
If C5 is not greater than 4%, the expression returns FALSE (or zero) and cancels out the rest of the formula, since zero times anything is zero.
