"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