| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help Qtr Mtch
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please Help Qtr Mtch
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please Help Qtr Mtch
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please Help Qtr Mtch
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please Help Qtr Mtch
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please Help Qtr Mtch
Kristi,
????? I thought we had come up with a formula that works: =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 ... 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please Help Qtr Mtch
I place the calculation in and result= 1.80 I think I need to remove the E:
Salary from the formula. If cell D is less than 3% then cell F is 100%, if cell D is 4%then cell F calculates 100% of 3% and .5%, if cell D is 5% or more then cell F calculates 100 of 3% and 2%/2. "Bernie Deitrick" wrote: Kristi, ????? I thought we had come up with a formula that works: =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 ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
