Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Student Creating Formula
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 ,(MIN(E2*0.04,F2) + MIN(E2*0.03, F2)) / 2) Example: Ann elects 1% total deducted is 180.02. Qtr Match is 180.02 Pleas help! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Student Creating Formula
Try the following formula:
=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))) Hope it helps. -- John C "KristiM" wrote: 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 ,(MIN(E2*0.04,F2) + MIN(E2*0.03, F2)) / 2) Example: Ann elects 1% total deducted is 180.02. Qtr Match is 180.02 Pleas help! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Student Creating Formula
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. "John C" wrote: Try the following formula: =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))) Hope it helps. -- John C "KristiM" wrote: 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 ,(MIN(E2*0.04,F2) + MIN(E2*0.03, F2)) / 2) Example: Ann elects 1% total deducted is 180.02. Qtr Match is 180.02 Pleas help! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Student Creating Formula
My error, I had the F column in dollar value of compensation, and not
percentage. This should work (and I just tested). =IF(DATEDIF(C2,DATE(YEAR(TODAY()),12,31),"y")<21,0 ,IF(F2<=0.03,F2*E2,MIN(0.03*E2+((F2-0.03)*E2)/2,0.03*E2+(0.02*E2)/2))) -- John C "KristiM" wrote: 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. "John C" wrote: Try the following formula: =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))) Hope it helps. -- John C "KristiM" wrote: 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 ,(MIN(E2*0.04,F2) + MIN(E2*0.03, F2)) / 2) Example: Ann elects 1% total deducted is 180.02. Qtr Match is 180.02 Pleas help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hi Everyone! Im a Student so please be kind... | Charts and Charting in Excel | |||
Pivot table and formula help??STUDENT NEED HELP.... | Excel Discussion (Misc queries) | |||
how to match Id and Student ID | Excel Worksheet Functions | |||
student directory | Excel Discussion (Misc queries) | |||
Trouble with formula on Student 2006 learning essentials template | Excel Worksheet Functions |