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.