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

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

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

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

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 -

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

"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

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.

wrote:

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.

joeu2004: That's pretty tedious instead of just using my formula
which can be coppied down a list of salesmen to give all their
commissions on the same page. Particularly if you assume the formula
won't change too often. Did you see a problem in my formula?
Because tax rates and brackets change at least every year, and there
are 4 tax "status" such as MFJ, MFS, HH, Single, with different
brackets, I actually use a chart such as yours with a VLOOKUP. For
the OP's problem I think a simiple formula is easier to apply.

Also, I think you meant '"Thus, the commission on 16000 is 1400, not
simply 2400 (15% of 16000) " instead of "1600 (10% of 16000)".

ed

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

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?

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