Help with greater than, less than

[johnb1979]

Hi all,

I've not posted in a while but very confident that you'll be able to answer this one for an amateur like me

I'm working on a commission calculator for a sales team. The brief is that the more a sales person sells, the % commission earned reduces in value depending on the value bracket they reach. Below is the price bracket;

1 - up to £18K = £800
2 - over £18k but less than £100k = 6% of the value between 18 & 100k
3 - over £100k but less than £250k = 3% of the value between 100 & 250K
4 - over £250k = 1% of the value over 250k (no cap)

I think I'm going to need separate formulas for each of these. Something else to consider is that the values may be variable (the % for example may change) so the formulas would need to reference a separate cell which I can alter the value without having to re-do the whole formula.

I hope this all makes sense and I'm sure one of you Excel Wizards will have this answered in no time at all!!

Please accept my thanks in advance - if you need any additional information or clarifications then please just let me know!

 

[Dave Patton]

What amount do you calculate for 250,000?
Try the following

Commission2020.xlsm
	A	B	C	D	E
1		Total sales	250,000.00
2		Commission	10,220.00
3		Commission Arithmetic	10,220.00
4
5
6		Bracket [rB]	Rate	Rate Delta [rR]
7					800.00
8		18,000	6%	6%	4,920.00
9		100,000	3%	-3%	4,500.00
10		250,000	1%	-2%	0.00
11			1E+308
12
1c

Cell Formulas
Range	Formula
C2	C2	=(C1>0)*800+SUMPRODUCT(--(C1>B8:B10),C1-B8:B10,D8:D10)
C3	C3	=SUM(E7:E10)
E7	E7	=(C1>0)*800
D8	D8	=C8-N(C6)
E8:E10	E8	=MAX(0,MIN($C$1,B9)-B8)*C8
D9:D10	D9	=C9-N(C8)
C11	C11	=BigNum

 

[JimM]

Here's my way, can't upload so will try and put into words

A1 = 18000
A2 = 100000
A3 = 250000

B1 = 800
B2 = 6%
B3 = 3%
B4 = 1%

B6 = Total Sales Amount

Commission formula would be

=IF(B6>A3,((B6-A3)*B4)+((A3-A2)*B3)+((A2-A1)*B2)+B1,IF(B6>A2,((B6-A2)*B3)+((A2-A1)*B2)+B1,IF(B6>A1,((B6-A1)*B2)+B1,B1)))

 

[johnb1979]

Hi both,

that's great, thank you!

I'll try both ways and see how I get on - thanks for coming back so quickly, much appreciated!