Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
FEMA gives my city a percent of disaster grants for administrative costs,
based upon a tiered system related to the amount of the awards, and I've been trying to figure a formula to determine these amounts. Problem: The administrative costs awards are as follows: 3% on the first $100,000 2% of the next $900,000 1% of the next $4,000,000 ..5% of everything in excess of $5,000,000 A figure of $6,000,000 ought to invoke all these percentages. Any ideas |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
hi
based on what you said and your chart, this is how i interpeted...... =SUM(0.02*100000)+(0.02*900000)+(0.01*400000)+(0.0 05*5000000) not sure but maybe it will give you ideas Regards FSt1 "Scott - Key West" wrote: FEMA gives my city a percent of disaster grants for administrative costs, based upon a tiered system related to the amount of the awards, and I've been trying to figure a formula to determine these amounts. Problem: The administrative costs awards are as follows: 3% on the first $100,000 2% of the next $900,000 1% of the next $4,000,000 .5% of everything in excess of $5,000,000 A figure of $6,000,000 ought to invoke all these percentages. Any ideas |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
I don't think that will do it. The formula needs to work for awards from
anywhere to $3,000 to $6m. Lets say we have a $150,000 award. The formula would need calculate 3% on the first $100,00, and an additional 2% on the remaining $50,000. The two calculations combined would tell me what FEMA would be sending us for administrative costs. Lest say we have a $2.5m award. The formula would need calculate 3% on the first $100,00, an additional 2% on the next $900k (between these first two steps, that covers the first million), then, an additional 1% on the next $4m (all three of these steps would cover the first $5m), then, an additional 0.5% on anything in excess of $5m Hope this is clearer than mud. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
Try this formula to get the general logic. Then you can simplify it
=IF(A15000000,(A1-5000000)*0.5%+4000000*1%+900000*2%+100000*3%,IF(A1 1000000,(A1-1000000)*1%+900000*2%+100000*3%,IF(A1100000,(A1-100000)*2%+100000*3%,A1*3%))) Tyro "Scott - Key West" <Scott - Key wrote in message ... FEMA gives my city a percent of disaster grants for administrative costs, based upon a tiered system related to the amount of the awards, and I've been trying to figure a formula to determine these amounts. Problem: The administrative costs awards are as follows: 3% on the first $100,000 2% of the next $900,000 1% of the next $4,000,000 .5% of everything in excess of $5,000,000 A figure of $6,000,000 ought to invoke all these percentages. Any ideas |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
See this:
http://www.mcgimpsey.com/excel/variablerate.html -- Biff Microsoft Excel MVP "Scott - Key West" <Scott - Key wrote in message ... FEMA gives my city a percent of disaster grants for administrative costs, based upon a tiered system related to the amount of the awards, and I've been trying to figure a formula to determine these amounts. Problem: The administrative costs awards are as follows: 3% on the first $100,000 2% of the next $900,000 1% of the next $4,000,000 .5% of everything in excess of $5,000,000 A figure of $6,000,000 ought to invoke all these percentages. Any ideas |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
Scott
=IF(C10<100000,C10*0.03,IF(C10<1000000,3000+0.02*( C10-100000),IF(C10<5000000,3000+18000+ (C10-1000000)*0.01,3000+18000+40000+0.05*(C10-5000000)))) Good luck. Ken Norfolk, Va. On Jan 15, 3:32*pm, Scott - Key West <Scott - Key wrote: FEMA gives my city a percent of disaster grants for administrative costs, based upon a tiered system related to the amount of the awards, and I've been trying to figure a formula to determine these amounts. Problem: The administrative costs awards are as follows: 3% on the first $100,000 2% of the next $900,000 1% of the next $4,000,000 .5% of everything in excess of $5,000,000 A figure of $6,000,000 ought to invoke all these percentages. Any ideas |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
Lose the SUM function. It's doing nothing in that equation.
-- David Biddulph "FSt1" wrote in message ... hi based on what you said and your chart, this is how i interpeted...... =SUM(0.02*100000)+(0.02*900000)+(0.01*400000)+(0.0 05*5000000) not sure but maybe it will give you ideas Regards FSt1 "Scott - Key West" wrote: FEMA gives my city a percent of disaster grants for administrative costs, based upon a tiered system related to the amount of the awards, and I've been trying to figure a formula to determine these amounts. Problem: The administrative costs awards are as follows: 3% on the first $100,000 2% of the next $900,000 1% of the next $4,000,000 .5% of everything in excess of $5,000,000 A figure of $6,000,000 ought to invoke all these percentages. Any ideas |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
Excellent! Tyro & Ken essentially came up with variants of the same formula
that worked marvelously. This question really exceeded my gray matter capabilities. It would have taken me at least a few long hot soaks in the tub to come up with what you guys were able to jot down in a couple of minutes. I thought I had a rather logical mind, but you guys must be pros. Thanks again, Scott Key West |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Driving me nuts trying to figure this formula
Looks kinda ugly, but
=(0.03*(MIN(A2,100000)))+(0.02*(MIN(900000,A2-(MIN(A2,100000))))) +(0.01*(MIN(4000000,A2-(MIN(A2,100000))-(MIN(900000,A2- (MIN(A2,100000)))))))+(0.005*(A2-(MIN(A2,100000))-(MIN(900000,A2- (MIN(A2,100000))))-(MIN(4000000,A2-(MIN(A2,100000))-(MIN(900000,A2- (MIN(A2,100000)))))))) On Jan 15, 3:32 pm, Scott - Key West <Scott - Key wrote: FEMA gives my city a percent of disaster grants for administrative costs, based upon a tiered system related to the amount of the awards, and I've been trying to figure a formula to determine these amounts. Problem: The administrative costs awards are as follows: 3% on the first $100,000 2% of the next $900,000 1% of the next $4,000,000 .5% of everything in excess of $5,000,000 A figure of $6,000,000 ought to invoke all these percentages. Any ideas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i know this is simple, but driving me nuts - formula | Excel Worksheet Functions | |||
Need Help, this is driving me nuts | Excel Discussion (Misc queries) | |||
question driving me nuts | New Users to Excel | |||
Sum and Count are driving me nuts!! | Charts and Charting in Excel | |||
Driving me nuts. Need more nested than 7 | Excel Discussion (Misc queries) |