|
Excel Formula - any help appreciated
Hello,
I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris |
Excel Formula - any help appreciated
I would create a table that I can use a vlookup on. This would allow
me to modify the bands and percentages easily when they change. x1 y1 5000 0.05 15000 0.10 20000 0.15 the commision formula becomes =vlookup(sales,$x$1:$y$3,2,true) Hope this helps, Horst On Nov 8, 8:21 am, Chris wrote: Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris |
Excel Formula - any help appreciated
I think you will need to amend the table, as follows:
0 0.05 5000 0.10 15000 0.15 20000 etc. Hope this helps. Pete flyswiftly wrote: I would create a table that I can use a vlookup on. This would allow me to modify the bands and percentages easily when they change. x1 y1 5000 0.05 15000 0.10 20000 0.15 the commision formula becomes =vlookup(sales,$x$1:$y$3,2,true) Hope this helps, Horst On Nov 8, 8:21 am, Chris wrote: Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris |
Excel Formula - any help appreciated
Create a table with your bandings. The revenue in one column and the
%commission in next column, Example given below... Enter these figures starting with 0 in the Cell A1 0 5% 5000 10% 10000 15% 15000 20% 20000 25% 25000 30% Enter the Total Revenue in the cell B10 ... Example $15000 To calculate your % commission based on the revenue earned ($15000) Enter this formula in the cell C10 ... =VLOOKUP(B10,A1:B6,2) What we have done here is we have defined a table which can be modified later based on your requirement, and then we have used the vlookup formula... The syntax for the vlookup formula is... VLOOKUP (lookup_value,table_array,col_index_num,range_look up) lookup_value - is the cell where we have entered the revenue table_array - the revenue & commission bandings table col_index_num - the column no. in the array where the commission % is defined, in our case column 2... so the value here will be 2 Let me know if this helps -- Krishna Viswanathan "Chris" wrote: Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris |
Excel Formula - any help appreciated
Right you are. Sorry for the confusion. Horst On Nov 8, 8:57 am, "Pete_UK" wrote: I think you will need to amend the table, as follows: 0 0.05 5000 0.10 15000 0.15 20000 etc. Hope this helps. Pete flyswiftly wrote: I would create a table that I can use a vlookup on. This would allow me to modify the bands and percentages easily when they change. x1 y1 5000 0.05 15000 0.10 20000 0.15 the commision formula becomes =vlookup(sales,$x$1:$y$3,2,true) Hope this helps, Horst On Nov 8, 8:21 am, Chris wrote: Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris- Hide quoted text -- Show quoted text - |
Excel Formula - any help appreciated
Or if you want a looong formula
=MIN(A15,5000)*5%+(A155000)*MIN(A15-5000,10000)*10%+(A1515000)*MIN(A15-15000,5000)*15%+(A1520000)*(A15-20000)*20% best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Chris" wrote in message ... Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris |
Excel Formula - any help appreciated
"Chris" wrote in message ... Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris Chris: I don't think any of the formulas given so far will do what you seem to want. Any manageable (simple) VLOOKUP formula will select one percentage to apply to total sales. I think you want the commision rate to apply only to the sales WITHIN each band. This is the way income tax "brackets" work so it is a common problem (at least in the USA). The following formula will apply the applicable % to the amuont of sales in each band. The constant subtracted in each step is the cumulative commission earned on the previous bands. If the total sales don't reach the next higher band, the constant subtracted makes that part of the formula ( and any higher bracket) zero or less. .. =MAX(.05*A,.10*A-250,.15*A-1250,.2*A-2000,etc.etc). When I do this I set up several columns with formulas that compute the constant for each bracket. I can e-mail you a small Excel file if you want. ed |
Excel Formula - any help appreciated
Chris wrote:
I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? It is always best to provide a numeric example of the answer you expect. That helps to resolve ambiguities in the English description. I assume that you mean 5% of the first 5000, plus 10% of the amount over 5000 up to 15000, etc. Thus, the commission on 16000 is 1400, not simply 1600 (10% of 16000). A straight-forward solution might be.... Create the following table (forgive me if the table does not align well): X Y Z 1 0 5% 0 2 5000 10% =Z1+Y1*(X2-X1) 3 15000 15% =Z2+Y2*(X3-X2) 4 20000 20% =Z3+Y3*(X4-X3) Then the commission can be computed as follows: =VLOOKUP(A1,X1:Z4,3) + VLOOKUP(A1,X1:Z4,2)*(A1-VLOOKUP(A1,X1:Z4,1)) where A1 contains the revenue. Column X is the upper limit of the __previous__ bracket ("band"). Column Y is the commission rate for the revenue __over__ the amount in Column X. Column Z is the __cumulative__ commission from the __previous__ brackets. Alternatively, relying on a paradigm proposed by McGimpsey et al, create the following table: X Y Z 1 0 5% =Y1 2 5000 10% =Y2-Y1 3 15000 15% =Y3-Y2 4 20000 20% =Y4-Y3 Then the commission can be computed as follows: =SUMPRODUCT(--(A1X1:X4), A1-X1:X4, Z1:Z4) where A1, column X and column Y are as above. Column Z is the __incremental__ commission rate for the revenue __over__ the amount in Column X. The formula effectively evaluates the following: (A1X1)*(A1-X1)*Z1 + (A1X2)*(A1-X2)*Z2 +.... Hope this helps. Caveat: Although I tested the formulas in an Excel spreadsheet, I made some last-minute editing changing. I hope I did not introduce any errors. Let me know if I did. |
Excel Formula - any help appreciated
|
Excel Formula - any help appreciated
Thanks very much guys, I'll now be able to pay some had working sales people!
Chris -- Chris "Chris" wrote: Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris |
Excel Formula - any help appreciated
Chris wrote: Thanks very much guys, I'll now be able to pay some had working sales people! Chris -- So, which formula did you use? |
Excel Formula - any help appreciated
Many thanks everyone and appologies for not explaining my query accurately
enough. There is no override once a bew bracket is reached (people always earn 5% up to £5000 and 10% of revenue between 5001 - 15000, etc). I used Eds and answers, all seemed to work. Many thanks again, I'm a novice at this and you've really helped. Chris -- Chris "Chris" wrote: Hello, I'm using Excel to calculate commission payments for staff, they are paid a % of revenue earned based on bandings achieved. For example: 5% of the first £5000 10% from £5001 - £15000 15% from £15001 - £20000 etc If I have a total revenue figure, how can I write a furmula which calculated the actual £commission earned based on the sliding scale above? Any help appreciated, -- Chris |
| All times are GMT +1. The time now is 03:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com