Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula help needed!
Hello everyone,
I would like to create a formula that would calculate a different levy charge dependent on the amount. The example is below and any help would be really appreciated. In circumstances where amounts owed do not exceed £100 or $100 12.5% charge will apply, 4% on the next £400 or $400, 2.5% on the next £1500 or $1500, 1% on the next £8000 or $8000 and 0.25% on any additional sum. In short different %'s will apply at different levels depending on monies mowed. I'm sure that there will be use of If/And but I'm not sure on the structure of the formula. I hope I haven't confused you all. Many thanks in advance, Pat Convey. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula help needed!
Pat,
with the amount owed in A1 try this =SUMPRODUCT(--(A1{0;100;500;2000;10000}), (A1-{0;100;500;2000;10000}), {0.125;-0.085;-0.015;-0.015;-0.0075}) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Pat Convey" wrote: Hello everyone, I would like to create a formula that would calculate a different levy charge dependent on the amount. The example is below and any help would be really appreciated. In circumstances where amounts owed do not exceed £100 or $100 12.5% charge will apply, 4% on the next £400 or $400, 2.5% on the next £1500 or $1500, 1% on the next £8000 or $8000 and 0.25% on any additional sum. In short different %'s will apply at different levels depending on monies mowed. I'm sure that there will be use of If/And but I'm not sure on the structure of the formula. I hope I haven't confused you all. Many thanks in advance, Pat Convey. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula help needed!
Sat, 24 Apr 2010 05:26:01 -0700 from Pat Convey
: Hello everyone, I would like to create a formula that would calculate a different levy charge dependent on the amount. Please don't multipost. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula help needed!
On Sat, 24 Apr 2010 05:26:01 -0700, Pat Convey
wrote: Hello everyone, I would like to create a formula that would calculate a different levy charge dependent on the amount. The example is below and any help would be really appreciated. In circumstances where amounts owed do not exceed £100 or $100 12.5% charge will apply, 4% on the next £400 or $400, 2.5% on the next £1500 or $1500, 1% on the next £8000 or $8000 and 0.25% on any additional sum. In short different %'s will apply at different levels depending on monies mowed. I'm sure that there will be use of If/And but I'm not sure on the structure of the formula. I hope I haven't confused you all. Many thanks in advance, Pat Convey. It's a little confusing. It would have been valuable had you given some examples of amount vs levy. But, I think you could set up a lookup table as follows: 0 $- 12.50% 100 $ 12.50 4.00% 500 $ 28.50 2.50% 2000 $ 66.00 1.00% 10000 $146.00 0.25% Using a table instead of a long IF formula would make future alterations much simpler. Note that the second column is the amount that would be levied on the amount in the first column. So if your table is in I1:K5, then J1: 0 J2: =J1+(I2-I1)*K1 and fill down to J5. Then, with your table NAME'd Tbl, and your amount in A1, the levy would be given by: =VLOOKUP(A1,Tbl,2)+(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel formula needed | Excel Worksheet Functions | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
Excel formula needed | Excel Worksheet Functions | |||
Excel Formula needed | Excel Discussion (Misc queries) | |||
Excel formula - help needed! | Excel Worksheet Functions |