formula for Working out commisions on a sliding scale?

[tonywatsonhelp]

Hi Everyone,

So i have a bit of a problem,
I need to work out an employees commission based on his sales,

we have up to 500 items at 10%
upto 1000 at 15%
over 1000 at 20%

so lets say in A7 is the number of sales 2000 for example, and in C7 is the amount sold for £25.00 So how can I do a formula that does this
first 500 at 10%
501 to 1000 at 15%
1001 up at 20%
= total commission

I have room for multiple cells if it easier?

thanks

Tony

 

[Toadstool]

Hi TonyWatsonHelp,

Does this work for you?

TonyWatsonHelp2.xlsx
	A	B	C	D	E	F	G	H	I
1								Sales	Commission
2								0	10%
3								501	15%
4								1001	20%
5
6	Sales		Unit Price		Commission
7	2000		25		£ 10,000.00
8	1		100		£ 10.00
9	500		100		£ 5,000.00
10	501		100		£ 7,515.00
11	999		100		£ 14,985.00
12	1000		100		£ 15,000.00
13	1001		100		£ 20,020.00
Sheet1

Cell Formulas
Range	Formula
E7:E13	E7	=A7*C7*INDEX($I$2:$I$4,MATCH(A7,$H$2:$H$4,1))

 

[Dave Patton]

Tony
With the information that you provided " lets say in A7 is the number of sales 2000 for example, and in C7 is the amount sold for £25.00 So how can I do a formula that does this",

what is the result that you expect?

 

[Alex Blakenburg]

See if either of these work for you.

The @MARK858 version is taken from here:-
Sliding Commission Scale (I know its been asked before!)

20210825 Commisions sliding scale.xlsx
	A	B	C	D	E	F	G
5	Sales Qty		Unit Price	Sale	Calc Commision	Calc Mark858
6	2000		25	50000	8125	8125
7	1		100	100	10	10
8	500		100	50000	5000	5000
9	501		100	50100	5015	5015
10	999		100	99900	12485	12485
11	1000		100	100000	12500	12500
12	1001		100	100100	12520	12520
13
Sheet1

Cell Formulas
Range	Formula
D6:D12	D6	=A6*C6
E6:E12	E6	=IF(A6>1000,(A6-1000)*0.2+(1000-500)*0.15+(500*0.1), IF(A6>500,(A6-500)*0.15+(500*0.1), A6*0.1))*C6
F6:F12	F6	=SUMPRODUCT(--(A6>{0;500;1000}), C6*(A6-{0;500;1000}), {0.1;0.05;0.05})