Complex formula example 401k Match

In the US, many companies match an employee’s retirement deferral up to a certain percent.

In Tier 2, the company matches 50% on deferrals between 4% and 6%.

After that, there’s no match.

Let’s look at how we can calculate the match for these two Tiers withIF statements.

Then in the next video, we’ll look at how we can simply the formulas.

So we’ll need to extend theIF functionto handle this by adding a value if FALSE.

When I copy this down, we’ll get the correct amounts for Tier 1.

For the value if FALSE, it’s a little more tricky.

When I copy the formula down, we have complete Tier 2 amounts.