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 LOVE your site. It helps me frequently as I have no formal training but I'm very logic oriented and this site is put together so very well and has assisted me so very much! You've improved the quality of my life. -Mike