Exceljet

Quick, clean, and to the point

This video is part of our video training library.

Simplified formula example - 401k Match

Tags 
Summary 
In this video, we simplify two formulas, by replacing several IF function with MIN function and boolean logic. This is a great trick to make annoyingly complex formulas easier to read and understand.
Video 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.

Both tiers use one or more IF statements, and the second formula is a bit complicated.

Let's look at how to simplify the formulas a bit.

=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 multiply 4% times B5.

So, 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 good to remove duplication in a formula when possible.

But we can also remove IF completely by using the MIN instead.

=MIN(C5,4%)*B5

Essentially, we take the smaller of C5 or 4%, and multiply 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%

In the outer IF, we check the deferral. If it's less than 4%, we're done. This means the entire match was handled in Tier 1, so Tier 2 is 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%. If, so, we subtract 4% and multiply by B5. If not, we just use 2% since two percent 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 MIN similar to what we did in Tier 1.

=IF(C5>4%,MIN(2%,C5-4%),0)*B5*50%

Take the smaller 2% or C5-4%, then multiply B5.

This is a simpler formula, but we can go one step further using boolean logic.

Note that C5>4% is a logical expression that returns either TRUE or FALSE. Now, in Excel, TRUE evaluates to 1, and FALSE evaluates to zero. 

That means we can remove IF and just multiply the expression times the rest of the formula:

=(C5>4%)*MIN(2%,C5-4%)*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.

Author 
Dave Bruns
I sincerely want to register my profound gratitude to Dave and Lisa. There are many features so unique about the tutorials in Exceljet: the simplicity, down-to-earth approach and the connection between the instructor and the learner. -John
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course