A formula for sliding scale percentage??????

[Paul]

Hi, I am still very much an Excel novice, barley finding my way, so please be
a little patient. I am trying to calculate a commission payment based on a
sliding percentage scale.

Let’s say cell B8 contains a salespersons total monthly gross profit, I want
to show their commission payment in cell D5

I require a formula which will calculate if the figure in B8 is less than
$3000 then 0% of the value of B8 will be calculated and shown in D5.

If between $3001 and $5000 then 5% will be shown in D5

If between $5001 and $7500 then 7.5% will be shown in D5

Etc, etc, up too anything over $20000 will be calculated at 20%

All calculations are retrospective to $1

I have tried creating cells H15:H23 with the different percentage amounts in
each but so far to no avail. Any assistance would be greatly appreciated.

=(B8)<3001>5000*$H$15,(B8)<5001>7500*($H$17),(B8)<7501>10000*($H$18),(B8)<10001>12500*($H$19),(B8)<12501>14999*($H$21),(B8)<15000*($H$23)

This is just one example of many, many, many, hours of frustration. Have
also tried the=IF true false thing!

Thanks Paul (Australia)

 

[JE McGimpsey]

One way:

=LOOKUP(B8,{0,0;3001,0.05;5001,0.075;20001,0.20})

This assumes that $3000 gets $0, but jumps to 7.5% at 3001 ($225.08).

If the rates should apply only to the differential amounts, see

http://www.mcgimpsey.com/excel/variablerates.html

 

[Paul]

Thanks for taking time on this. Using your sugestion I get, the formula you
typed contains an error. Then under my cell

LOOKUP(lookup_value, lookup_vector, [result_vector])
LOOKUP(lookup_value, array)

Unable to open your link.

Paul

 

[JE McGimpsey]

Since you didn't say what error you got with the formula, it's hard to
know how to help - the syntax is fine, and it produces the results that
I believe you want.

The link shouldn't have had the last s:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Paul]

With a little messing around it now does what I need. Thank you very, very
much.
Paul

Since you didn't say what error you got with the formula, it's hard to
know how to help - the syntax is fine, and it produces the results that
I believe you want.

The link shouldn't have had the last s:

http://www.mcgimpsey.com/excel/variablerate.html

Thanks for taking time on this. Using your sugestion I get, the formula you
typed contains an error. Then under my cell

LOOKUP(lookup_value, lookup_vector, [result_vector])
LOOKUP(lookup_value, array)

Unable to open your link.