Sliding Commission Scale (I know its been asked before!)

[AmberJ]

Hi all-

I have looked through the multiple posts that have been done on sliding commission scales, and I am afraid I just can't work out how to modify them so they apply to me. Sorry, not particularly excel savvy at all. I really have tried!!

I am hoping I can stretch your all patience by asking if someone could very kindly prepare a formula for the scale I need?

This is the scale:

On the first $10,000, commission of 40%
On $10,001- $20,000, commission of 30%
On $20,001- $30,000, commission of 20%
On $30,001 and above, commission of 10%


I would really, really appreciate your help. Thank you so much.

 

[MARK858]

Using a table...

xxxbbb.xlsb
	A	B	C	D
1	25000
2	0	40%	40%	8000
3	10000	30%	-10%
4	20000	20%	-10%
5	30000	10%	-10%
Sheet2

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(A1>$A$2:$A$5),(A1-$A$2:$A$5), $C$2:$C$5)
C2	C2	=B2
C3:C5	C3	=B3-B2

with just a formula

xxxbbb.xlsb
	A	B	C	D
1	25000
2
3				8000
Sheet2

Cell Formulas
Range	Formula
D3	D3	=SUMPRODUCT(--(A1>{0;10000;20000;30000}), (A1-{0;10000;20000;30000}), {0.4;-0.1;-0.1;-0.1})

 

[AmberJ]

Thank you so much

 

[MARK858]

You're welcome, please note that the commission changes at anything greater than the Max i.e. if it is $20000 and obviously currency then it would change at $20000.01 and not $20001 as you would have a gap of $1.