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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

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

New Member
Joined
Sep 7, 2002
Messages
16

ADVERTISEMENT

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

Del

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

Active Member
Joined
Feb 20, 2002
Messages
441
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

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

Forum statistics

Threads
1,144,275
Messages
5,723,448
Members
422,497
Latest member
dougy99

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
Top