Student Creating Formula
 

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!

Student Creating Formula
 

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


"KristiM" wrote:

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!

Student Creating Formula
 

I place the calculation in and result= 1.80 I think I need to remove the E:
Salary from the formula. If cell D is less than 3% then cell F is 100%, if
cell D is 4%then cell F calculates 100% of 3% and .5%, if cell D is 5% or
more then cell F calculates 100 of 3% and 2%/2.

"John C" wrote:

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


"KristiM" wrote:

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!

Student Creating Formula
 

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)))

--
John C


"KristiM" wrote:

I place the calculation in and result= 1.80 I think I need to remove the E:
Salary from the formula. If cell D is less than 3% then cell F is 100%, if
cell D is 4%then cell F calculates 100% of 3% and .5%, if cell D is 5% or
more then cell F calculates 100 of 3% and 2%/2.

"John C" wrote:

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


"KristiM" wrote:

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!