Calculating mult band sales commission formula

[R.gillespie]

Hi everyone,

I am trying to setup a excel sheet to calculate sales reps commissions, the
more they sell the higher the banding becomes.
<6500 profit = 5% commission
between 6501 & 8000 = 7%
between 8001 & 10'000 = 10
10k to 12.5k = 12%
12.5k to 15k = 15%
over 15k = 20%

Example -
if they earn £10'000 - they get 5% first £6500 (£325), 7% of the next £1500
(£134) etc

Can anyone help me with a formula that will do this for me?

Thanks

Richard

 

[Ragdyer]

John McGimpsey has a web page on this subject:

http://www.mcgimpsey.com/excel/variablerate.html

 

[ShaneDevenshire]

Hi,

Create the following table by enter the data in cells A1:B6.

0 5%
6500 7%
8000 10%
10000 12%
12500 15%
15000 20%

Suppose you want to calculate the rate for 9000 in cell D1, enter the formula

=VLOOKUP(D1,$A$1:$B$6,2)

in cell E1 or whereever.

Note that your example is inconsistant. You show <6500 and 6501 - what
happens with 6500? no commission. Since I am not exactly sure which way you
want to go you may need to modify the table a litte. For example you many
need to change 6500 to 6501 and so on.