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.
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.
The IF function can perform a logical test and return 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....
Download 200+ Excel Shortcuts
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