Varied rates of commission spreadsheet, please help!

[Jaminator]

Hi,

First time i have signed up to a forum, I can't seem to find an answer online, and have spent hours trying to figure this out. I need a spreadsheet that calculates varied rates of commission.

If a sales person bills £45,000 (above a target threshold), they will be paid on the following:

5% commission on first £5,000
10% commission on next £5,000
20% commission on next £10,000
30% commission on next £20,000
40% commission on remaining (so 40% on £5,000 in this example)

*These rates are set, if you bill £100 or £100,000 above TH*

So if someone bills £100 above TH, they earn £5. If someone bills £45,000, they earn £10,750

I thought this would be simple at first, and had a basic spreadsheet working for my £45,000. Then i tried different figures, and well, it was so wrong, not even close, I just can't get a formula that works

If anyone could help, I will be forever in your debt!

Thank you

Jack

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	Amount a	100.00	5.00		Brackets	Rates	Delta
2	Amount b	45,000.00	10,750.00		0	5%	5%
3					5,000	10%	5%
4					10,000	20%	10%
5					20,000	30%	10%
6					40,000	40%	10%
1bb

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(B1>rB),B1-rB,rDelta)
C2	=SUMPRODUCT(--(B2>rB),B2-rB,rDelta)
G2	=F2-N(F1)

Named Ranges
Name	Refers To	Cells
Delta	='1b'!$F$3:$F$6
rB	='1bb'!$E$2:$E$6
rDelta	='1bb'!$G$2:$G$6

 

[Jaminator]

Thank you! Dont really understand what is going on there. I got one to work with, 'if' and 'max' in the end, trial and error, this looks more efficient, Thanks!