Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formula Help Please!
I can get 2/3 of the formula done, but I'm stumped on the middle section.
Any help is greatly appreciated! I'm trying to calculate a benefit amount based on earnings, as follows: 66.7% of the first $3,000 of monthly earnings 55% of the next $2,500 of monthly earnings 40% of any balance The maximum result is $3,500. Thanks to any and all! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formula Help Please!
Assuming your monthly earnings is in A2:
=MIN(3500,MAX(0,(A2-5500))*40%+MIN(A2,3000)*66.7%+MIN(2500,MAX(0,A2-3000))*55%) Note that the 5500 comes from the sum of your first two conditions. You could of course replace the cutoff points and percentages with cell references, if desired. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "CharlieGirl" wrote: I can get 2/3 of the formula done, but I'm stumped on the middle section. Any help is greatly appreciated! I'm trying to calculate a benefit amount based on earnings, as follows: 66.7% of the first $3,000 of monthly earnings 55% of the next $2,500 of monthly earnings 40% of any balance The maximum result is $3,500. Thanks to any and all! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formula Help Please!
=MAX(3500,IF(A15500,2001+1375+0.4*(A1-5500),IF(A13000,2001+0.55*(A1-3000),0.667*A1)))
Vaya con Dios, Chuck, CABGx3 "CharlieGirl" wrote: I can get 2/3 of the formula done, but I'm stumped on the middle section. Any help is greatly appreciated! I'm trying to calculate a benefit amount based on earnings, as follows: 66.7% of the first $3,000 of monthly earnings 55% of the next $2,500 of monthly earnings 40% of any balance The maximum result is $3,500. Thanks to any and all! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formula Help Please!
Thank you! Exactly what I was hoping for!!
"Luke M" wrote: Assuming your monthly earnings is in A2: =MIN(3500,MAX(0,(A2-5500))*40%+MIN(A2,3000)*66.7%+MIN(2500,MAX(0,A2-3000))*55%) Note that the 5500 comes from the sum of your first two conditions. You could of course replace the cutoff points and percentages with cell references, if desired. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "CharlieGirl" wrote: I can get 2/3 of the formula done, but I'm stumped on the middle section. Any help is greatly appreciated! I'm trying to calculate a benefit amount based on earnings, as follows: 66.7% of the first $3,000 of monthly earnings 55% of the next $2,500 of monthly earnings 40% of any balance The maximum result is $3,500. Thanks to any and all! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formula Help Please!
Sorry, my bad, shoulda been
=Min(3500,IF(A15500,2001+1375+0.4*(A1-5500),IF(A13000,2001+0.55*(A1-3000),0.667*A1))) Vaya con Dios, Chuck, CABGx3 "CLR" wrote: =MAX(3500,IF(A15500,2001+1375+0.4*(A1-5500),IF(A13000,2001+0.55*(A1-3000),0.667*A1))) Vaya con Dios, Chuck, CABGx3 "CharlieGirl" wrote: I can get 2/3 of the formula done, but I'm stumped on the middle section. Any help is greatly appreciated! I'm trying to calculate a benefit amount based on earnings, as follows: 66.7% of the first $3,000 of monthly earnings 55% of the next $2,500 of monthly earnings 40% of any balance The maximum result is $3,500. Thanks to any and all! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional if formula. | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula, Conditional Formula Needed | Excel Discussion (Misc queries) | |||
Conditional Formula to indicate Formula in cell | New Users to Excel | |||
Conditional Formula | Excel Worksheet Functions |