Percentage Formula

[KristiM]

I am trying to create a formula that will create the following: Column C
Date of birth mm/dd/yyyy, Column D (elected percentage) Column E
Compensation, Column G Emp contribution . The match 100% of 1st 3% and 50% of
next 2% No match over 4%. If the Column D (elected percentage) is less than
3% calculate at 100%, but if column D is more than 3%, then 2% is at 50%.
Also no match if employee is under 21 of age at 12/31/08.
I have tried =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0,

min(G2,E2*3%) + 50%*max(0, min(G2-E2*3%,E2*2%)))

.. This formula is pulling the percentage amount from the compensation.

Please help!


Thank you for your assistance.

 

[Joel]

You need to change percentage to a decimal number

=if(datedif(C2,date(year(today()),12,31),"y") < 21, 0,
min(G2,E2*.03) + .5*max(0, min(G2-E2*.03,E2*.02)))

 

[Bernie Deitrick]

Kristi,

Try

=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y") < 21, 0,MIN(D2,3%)*E2 + 50%*MAX(0,MIN(D2-3%,2%)*E2))

I'm not sure what column G has to do with it.

HTH,
Bernie
MS Excel MVP

 

[KristiM]

Column G is the emp contributions. The match is calculated from the emp
contribution. That is why I am using column G. I need a formula that will
look at col D and say if less than 3% calculate 100% of Col D and if more
than 3% then calculate 100% for first 3% then 50% of 2%. Also look at date
of brith if less than 21 no match.

Thank you for your feedback

 

[Bernie Deitrick]

Kristi,

Yes, but if the employee is contributing 7%, then the match doesn't depend on column G, just on D
and E, as in my formula. Column G should just be

=D2*E2

HTH,
Bernie
MS Excel MVP