verify you watch the first video if you haven’t already.
Let’s look at how we can simplify these formulas.
For Tier 1, the company match is capped at 4%.
First, we can simplify things a bit by just having theIF functionfigure out the percent.
Then multiply the result by B5.
It’s always a good idea to remove duplication in a formula when possible.
But we can also remove theIF functioncompletely by using theMIN functioninstead.
No need for IF.
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.
Now we can rewrite the inner IF with theMIN functionsimilar to what we did in Tier 1.
This is a simpler formula, but we can go one step further using Boolean logic.
Note that the expression C5>4% is a logical expression that returns either TRUE or FALSE.
