# Percentage formula problem?

#### Del

##### New Member
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...

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)

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.

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

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 ??

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)

=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

I dont know how the hell you people
but the last two worked fine.
Thank you very much.

Replies
1
Views
160
Replies
5
Views
609
Replies
2
Views
120
Replies
1
Views
709
Replies
8
Views
246

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?

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