Student Creating Formula

[KristiM]

Trying to calculate Qtr 401k mtch. Mtch is 100% of the first 3% of deferred
compensation and 50% of the next 2%. D: Defer %, E: Salary, F: Deferred
Amount

=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0,(MIN(E2*0.04,F2) +
MIN(E2*0.03, F2)) / 2)

Example: Ann elects 1% total deducted is 180.02. Qtr Match is 180.02

Pleas help!

 

[John C]

Try the following formula
=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0,IF(D2<=0.03,D2*E2,MIN(0.03*E2+((D2-0.03)*E2)/2,0.03*E2+(0.02*E2)/2)))

Hope it helps.

 

[John C]

My error, I had the F column in dollar value of compensation, and not
percentage.

This should work (and I just tested).

=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0,IF(F2<=0.03,F2*E2,MIN(0.03*E2+((F2-0.03)*E2)/2,0.03*E2+(0.02*E2)/2)))