The company I work for will match employees 401K with the following: The
first 3% the company matches 100%, the 4th and 5th % the company will match
50%. Does anyone know a formula that will calculate this, I need to figure
this semi-monthly.

Can't you just multiply the amount invested by .04 ?


--
intruder9
------------------------------------------------------------------------
intruder9's Profile: http://www.excelforum.com/member.php...o&userid=30107
View this thread: http://www.excelforum.com/showthread...hreadid=502467

Trish,

With the salary in cell A2, and the percentage 401K constribution in B2, use this formula in C2:

=IF(B2=5%,A2*4%,IF(B2<=3%,A2*B2,A2*3%+A2*(B2-3%)/2))

HTH,
Bernie
MS Excel MVP


"Trish" wrote in message
...
The company I work for will match employees 401K with the following: The
first 3% the company matches 100%, the 4th and 5th % the company will match
50%. Does anyone know a formula that will calculate this, I need to figure
this semi-monthly.

"Trish" wrote:
The company I work for will match employees 401K with the
following: The first 3% the company matches 100%, the 4th
and 5th % the company will match 50%. Does anyone know
a formula that will calculate this, I need to figure this
semi-monthly.

I presume you mean that anything above 3% and less than or
equal to 5% is matched at 50%. If the salary is A2 and the
percentage contribution is in B2:

=A2*MIN(3%,B2) + 50%*A2*MAX(0,MIN(2%,B2-3%))

or equivalently:

=A2*(MIN(3%,B2) + 50%*MAX(0,MIN(2%,B2-3%)))

You probably want to put all that inside ROUNDDOWN(...,0)
or ROUNDDOWN(...,2) to round down to dollars or cents.

However, if you mean that anything under 4% is matched
100% and anything between 4% and 5% inclusive is matched
at 50%, that is harder. Post again if this is your intent.