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.
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.
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...