Percentage formula problem?

[Del]

Does anyone have a solution to this following problem.
Charged amount upto $50 @ 10%
Between $50 & $200 @ 5%
Between $200 & $1000 @ 2%
Over $1000 @ 1%
Example: If i charged an amount for $500 I would like a formula that calculates the first $50 @ 10%
the next $150 @ 5%
the last $300 @ 2%

I guess i'll need a formula that calculates 3 different rates for 1 value.
Being a newbie, any help would be greatfull.

Thanks Chris Davison for solving my previous problem, vlookup worked great.

 

[DonkeyOte]

You may struggle to put this in one cell as you can only embed 7 ifs.

You could keep the stratas separate and do calculations accordingly

e.g

up to $50
formula if (x > 50, 50*10%, x * 10%)

Between $50 & $200
formula if (x < 50, 0, if( x > 200, 150*5%, (x-50)* 5%))

and so on...

 

[Andrew Poulsom]

There's probably a shorter formula but:

1. In A1:A4 type 50, 200, 1000, 99999.
2. In B1:B4 type 10%, 5%, 2%, 1%
3. In C1 type =A1*B1
4. In C2 type =(A2-A1)*B2 and copy to C3:C4
5. In A6 type:

=SUMIF($A$1:$A$4,"<"&$A6,$C$1:$C$4)+(A$6-INDEX($A$1:$A$4,MATCH(A$6,$A$1:$A$4,1),1))*INDEX($A$1:$B$4,MATCH(A$6,$A$1:$A$4,1)+1,2)

 

[Del]

Thanks LASW10 for your reply.
Would i be correct in saying that my example
of $500 has three IF calculations in it?
If so, then how would this calculation read if it were in one cell.
The reason I ask is that my final table may contain less than seven IF calculations.
Excel formulas for me are quite difficult when your thick as a brick.

 

[Del]

Thanks Andrew, i'll try lasw10's and your formulas later.

 

[Del]

I've just tried your method Andrew,
when copying the final string of your formula to cell A6, it says
"! Excel cannot calculate a formula, cell references in the formula refer to the formulas result, creating a circular reference.
If you accidently created a circular reference click ok."
Also which cell do i insert the amount to receive the appropriate percent rate in dollars ??

 

[Scott R]

On 2002-09-10 07:44, Andrew Poulsom wrote:
There's probably a shorter formula but:

1. In A1:A4 type 50, 200, 1000, 99999.
2. In B1:B4 type 10%, 5%, 2%, 1%
3. In C1 type =A1*B1
4. In C2 type =(A2-A1)*B2 and copy to C3:C4
5. In A6 type:

=SUMIF($A$1:$A$4,"<"&$A6,$C$1:$C$4)+(A$6-INDEX($A$1:$A$4,MATCH(A$6,$A$1:$A$4,1),1))*INDEX($A$1:$B$4,MATCH(A$6,$A$1:$A$4,1)+1,2)

The above won't work for certain values such as 1000.

Building on Andrew's post:
1. In A1:A4 type 0, 50, 200, 1000.
2. In B1:B4 type 10%, 5%, 2%, 1%
3. In C2 type =A2*B1 (C1 is 0 or blank)
4. In C3 type =(A3-A2)*B2 and copy to C4
5. In A6 type the value you want evaluated.

In any other cell enter this formula:
=SUMIF(A1:A4,"<="&A6,C1:C4)+(A6-VLOOKUP(A6,A1:A4,1))*VLOOKUP(A6,A1:B4,2)

 

[Mark W.]

How about this...

=CHOOSE(MATCH(A1,{0,50.01,200.01,1000.01}),A1*0.1,5+(A1-50)*0.05,12.5+(A1-200)*0.02,28.5+(A1-1000)*0.01)

where A1 contains your 'Charged amount'.
This message was edited by Mark W. on 2002-09-10 11:34

 

[Del]

Thank you all for your replys.
I dont know how the hell you people
get your head around theses calculations,
but the last two worked fine.
Thank you very much.