Percentage Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KristiM - 27 May 2008 14:38 GMT

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.

Reply to this Message

Joel - 27 May 2008 15:34 GMT

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

> 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
[quoted text clipped - 9 lines]
>
> Thank you for your assistance.

Reply to this Message

David Biddulph - 27 May 2008 23:33 GMT

Why would that make a difference, Joel?

Signature

David Biddulph

> You need to change percentage to a decimal number
>
[quoted text clipped - 16 lines]
>>
>> Thank you for your assistance.

Reply to this Message

Bernie Deitrick - 27 May 2008 15:38 GMT

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

>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
[quoted text clipped - 9 lines]
>
> Thank you for your assistance.

Reply to this Message

KristiM - 27 May 2008 16:35 GMT

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

> Kristi,
>
[quoted text clipped - 21 lines]
> >
> > Thank you for your assistance.

Reply to this Message

Bernie Deitrick - 27 May 2008 16:49 GMT

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

> 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
[quoted text clipped - 30 lines]
>> >
>> > Thank you for your assistance.

Reply to this Message