 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008401k Match
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. One way: H2: =IF(DATEDIF(C2,DATE(2008,12,31), "y") < 21, 0, (MIN(E2*0.05, G2) + MIN(E2*0.03, G2)) / 2) > 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. [quoted text clipped - 3 lines] > > Thank you for your assistance. Thank you for so much for the response. I will try this formula tonight for my spreadsheet. > One way: > [quoted text clipped - 8 lines] > > > > Thank you for your assistance. > 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%). No match over 4% since 50% of 2% will be 1% with 3% at 100% total 4% match awarded. > > I am trying to create a formula that will create the following: > > Column E Compensation, Column G Emp contribution, and [quoted text clipped - 10 lines] > on the end of the current year, not always 12/31/2008. Also, I assume > you mean no match over 5% (3% + 2%). 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, > min(G2,D2*3%) + 50%*max(0, min(G2-D2*3%,D2*2%))) > > I am trying to create a formula that will create the following: > > Column E Compensation, Column G Emp contribution, and [quoted text clipped - 10 lines] > on the end of the current year, not always 12/31/2008. Also, I assume > you mean no match over 5% (3% + 2%). > 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 ----- > After clarification the match should be calculated from Column G Emp > contribution not Column E Compensation. I will need to include Column D [quoted text clipped - 21 lines] > > on the end of the current year, not always 12/31/2008. Also, I assume > > you mean no match over 5% (3% + 2%). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.