Percentage formula problem?

Del

New Member
Joined
Sep 7, 2002
Messages
16
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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...
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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 ??
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,219,006
Messages
6,145,713
Members
450,635
Latest member
Rookie3510

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top