Excel Formula - any help appreciated

[Guest]

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,

 

[flyswiftly]

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

 

[Pete_UK]

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

 

[Guest]

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_lookup)
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

 

[flyswiftly]

Right you are. Sorry for the confusion.

Horst

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

 

[Bernard Liengme]

Or if you want a looong formula
=MIN(A15,5000)*5%+(A15>5000)*MIN(A15-5000,10000)*10%+(A15>15000)*MIN(A15-15000,5000)*15%+(A15>20000)*(A15-20000)*20%
best wishes

 

[ed]

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

 

[joeu2004]

I'm using Excel to calculate commission payments for staff, they are paida
% 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(--(A1>X1: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:

(A1>X1)*(A1-X1)*Z1 + (A1>X2)*(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.

 

[ed]

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

 

[Guest]

Thanks very much guys, I'll now be able to pay some had working sales people!

Chris

 

[ed]

Thanks very much guys, I'll now be able to pay some had working sales people!

Chris
--

So, which formula did you use?

 

[Guest]

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 (e-mail address removed) answers, all seemed to work.

Many thanks again, I'm a novice at this and you've really helped.

Chris