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 ,IF(D2<=0.03,D2*E2,MIN(0.03*E2+((D2-0.03)*E2)/2,0.03*E2+(0.02*E2)/2)))

Example: Ann elects 1% total deducted is 180.02. Qtr Match is 180.02

Pleas help!


"Bernie Deitrick" wrote:

What you were doing originally had this:

MIN(G2*3%,D2*2%)

but D2 is a percent - and 2% of that is irrelevant.

Anyway, the 2% is not 'outside' the parens. Here's the calc:

G2*(MIN(D2,3%)+50%*MAX(0,MIN((D2-3%),2%))))

The key is evaluating this:

MIN((D2-3%),2%)

That compares the amount above 3% but limits it to 5%

If D2 is 4%, it evaluates to 1%
If D2 is 5% or higher, it evaluates to 2%

BUT, if D2 is below 3%, it evaluates to a negative%
So, this part prevents that:

MAX(0,MIN((D2-3%),2%)))

MAX(0,Neg%)

evaluates to 0 for cases where D2 is less than 3%.


HTH,
Bernie
MS Excel MVP


"KristiM" wrote in message
...
Thank you so much that works! Do you mind explaining why placing the 2%
outside the parenthesis worked.

"Bernie Deitrick" wrote:

Kristi,

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

HTH,
Bernie
MS Excel MVP


"KristiM" wrote in message
...
I was provided with the following formula to calculate qtrly match:
=IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0 ,G2*MIN(D2,3%)+50%*MAX(0,MIN(G2*3%,D2*2%))).
The problem is that this formula is not calculating the correct amount for
anything over 4%. Match is 100% of the first 3% and 50% of the second 2%, so
if contribute 5% we only match 4% (3%(100%) + 1%( 50%- 2%)
Example: John contributes 10% 1st qtr amt $1514.43= $60.58. The formula
shown above calcs $45.43. What am I doing wrong?