401k Match

[KristiM]

I am trying to create a formula that will create the following: Column E
Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy.
The match 100% of 1st 3% and 50% of next 2% No match over 4%. Also no match
if employee is under 21 of age at 12/31/08.
I have tried IF....but I receive an error. Please help!

Thank you for your assistance.

 

[JE McGimpsey]

One way:

H2: =IF(DATEDIF(C2,DATE(2008,12,31), "y") < 21, 0, (MIN(E2*0.05,
G2) + MIN(E2*0.03, G2)) / 2)

 

[KristiM]

Thank you for so much for the response. I will try this formula tonight for
my spreadsheet.

 

[joeu2004]

I am trying to create a formula that will create the following:
 Column E Compensation, Column G Emp contribution, and
C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and
50% of next 2% No match over 4%.  Also no match
if employee is under 21  of age at 12/31/08.

Does the following work for you:

=if(datedif(C2,date(year(today()),12,31),"y") < 21, 0,
min(G2,E2*3%) + 50%*max(0, min(G2-E2*3%,E2*2%)))

I took the liberty of assuming you really want to calculate age based
on the end of the current year, not always 12/31/2008. Also, I assume
you mean no match over 5% (3% + 2%).

 

[KristiM]

No match over 4% since 50% of 2% will be 1% with 3% at 100% total 4% match
awarded.

 

[KristiM]

After clarification the match should be calculated from Column G Emp
contribution not Column E Compensation. I will need to include Column D
elected percentage in the formula. 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%.

Should the formula be:

=if(datedif(C2,date(year(today()),12,31),"y") < 21, 0,

 

[joeu2004]

After clarification the match should be calculated from Column G Emp
contribution not Column E Compensation. I will need to include Column D
elected percentage in the formula. 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%.

I assume that "100$" is a typo, and you mean 100%. Also, you do not
say what happens when the elected percentage is __equal__ to 3%. I
will assume you mean "less than or equal to" 3%.

It appears that you are trying to compute a matching percentage, not a
matching amount. Frankly, I don't know why you don't simply compute
the matching amount. It is simple, namely:

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

But if you insist on computing a matching percentage, try the
following:

min(D2,3%) + 50%*max(0,D2-3%)

If you understand the second formula, note that the first expression
can be simplified to:

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

Plug whichever expression fits your need into the IF formula below:

=if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, expression)

Does that answer your question?


----- original posting -----