If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A formula for sliding scale percentage??????
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)30015000*$H$15,(B8)50017500*($H$17),(B8) 750110000*($H$18),(B8)1000112500*($H$19),(B8)1 250114999*($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) |
#2
|
|||
|
|||
A formula for sliding scale percentage??????
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 In article , Paul wrote: 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)30015000*$H$15,(B8)50017500*($H$17),(B8) 750110000*($H$18),(B8)1000 112500*($H$19),(B8)1250114999*($H$21),(B8)1500 0*($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) |
#3
|
|||
|
|||
A formula for sliding scale percentage??????
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" wrote: 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 In article , Paul wrote: 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)30015000*$H$15,(B8)50017500*($H$17),(B8) 750110000*($H$18),(B8)1000 112500*($H$19),(B8)1250114999*($H$21),(B8)1500 0*($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) |
#4
|
|||
|
|||
A formula for sliding scale percentage??????
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 In article , Paul wrote: 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. |
#5
|
|||
|
|||
A formula for sliding scale percentage??????
With a little messing around it now does what I need. Thank you very, very
much. Paul "JE McGimpsey" wrote: 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 In article , Paul wrote: 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. |
Thread Tools | |
Display Modes | |
|
|