Commission Per Sale + Tiers

[TradieMike]

Hi Guys,

I was hoping you'd be able to help me with a formula to calculate monthly commissions for my team.

My team are paid per sale with the amount of commission increasing at different tiers.

0-5 sales = $0 per sale
6-55 sales = $25 per sale
56+ sales = $50 per sale

55 would equal to 100% of target and would result in $1,250 commision and then ever sale after that is worth $50 so for example if the achieve 56 sales then their total commission would be $1,300.

So what I'm looking for (if possible) is a formula that would allow me to just enter the amount of sales next to the agent (as show below) and it do that calculation for me.

Any help would be appreciated.

Cheers,
Mike

 

[Toadstool]

Hi TradieMike,

Does this do it for you?

Book1
	B	C	D
2	Commission Table
3	Sales Tiers	Tier Minimum	Payout Rate Per Sale
4	0-5	0	$0
5	6-55	6	$25
6	56+	56	$50
7
8
9		Sales Amount	Payout
10	Agent 1	81	$3,200
11	Agent 2	47	$1,050
12	Agent 3	58	$1,475
13	Agent 4	62	$1,775
14	Agent 5	55	$1,250
Sheet1

Cell Formulas
Range	Formula
D10:D14	D10	=(MAX(C10-($C$6-1),0)*$D$6)+(MAX(C10-($C$5-1),0)*$D$5)

 

[mrshl9898]

Like this?

=IF(C10<6,0,IF(C10<56,(56-C10)*25,1250+((C10-55)*50)))

 

[AhoyNC]

Another option.
See this link for explanation.

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

Book1
	A	B	C
1	Commission Table
2	Tier Min	Tier Max	Payout Rate Per Sale
3	0	5	$0
4	6	55	$25
5	56	And Over	$50
6
7
8		Sales Amount	Commission
9	Agent 1	81	$2,550
10	Agent 2	47	$1,050
11	Agent 3	58	$1,400
12	Agent 4	56	$1,300
13	Agent 5	55	$1,250
14	Agent 6	5	$0
Sheet3

Cell Formulas
Range	Formula
C9:C14	C9	=SUMPRODUCT(--(B9>({5,55})),(B9-{5,55}),{25,25})

 

[TradieMike]

Thank you guys, you're life savers.

 

[MARK858]

Thank you guys, you're life savers.

I would go with the formula that AhoyNC posted as there are anomalies in the other formulas.

Book1
	B	C	D	E	F
3	Sales Tiers	Tier Minimum
4	0-5	0	0
5	6-55	6	25
6	56+	56	50
7
8			Payout
9		Sales Amount
10	Agent 1	81	3200	2550	2550
11	Agent 2	47	1050	225	1050
12	Agent 3	58	1475	1400	1400
13	Agent 4	62	1775	1600	1600
14	Agent 5	55	1250	25	1250
15	Agent 6	5	0	0	0
Sheet3

Cell Formulas
Range	Formula
D10:D15	D10	=(MAX(C10-($C$6-1),0)*$D$6)+(MAX(C10-($C$5-1),0)*$D$5)
E10:E15	E10	=IF(C10<6,0,IF(C10<56,(56-C10)*25,1250+((C10-55)*50)))
F10:F15	F10	=SUMPRODUCT(--(C10>({5,55})),(C10-{5,55}),{25,25})

 

[Dave Patton]

Book1
	A	B	C	D
1	Commission Table
2	Tier Min		Payout Rate Per Sale	Rate Diff
3	0		0.00	0.00
4	5		25.00	25.00
5	55		50.00	25.00
6
7
8		Sales Amount	Commission
9	Agent 1	81	2,550.00	2,550.00
10	Agent 2	47	1,050.00	1,050.00
11	Agent 3	58	1,400.00	1,400.00
12	Agent 4	56	1,300.00	1,300.00
13	Agent 5	55	1,250.00	1,250.00
14	Agent 6	5	0.00	0.00
3a

Cell Formulas
Range	Formula
D3:D5	D3	=C3-N(C2)
C9:C14	C9	=SUMPRODUCT(--(B9>rB),(B9-rB),rDiff)
D9:D14	D9	=(B9>5)*(B9-5)*25+(B9>55)*(B9-55)*25

 

[MARK858]

Hi Dave, are rB and rDiff named ranges?

 

[Dave Patton]

Hello Mark858. Yes rB and rDiff are named ranges.

I thought the Named Ranges would show on the post but I did not check.
I removed the Names.

Book1
	A	B	C	D
1	Commission Table
2	Tier Min		Payout Rate Per Sale	Rate Diff
3	0		0.00	0.00
4	5		25.00	25.00
5	55		50.00	25.00
6
7
8		Sales Amount	Commission
9	Agent 1	81	2,550.00	2,550.00
10	Agent 2	47	1,050.00	1,050.00
11	Agent 3	58	1,400.00	1,400.00
12	Agent 4	56	1,300.00	1,300.00
13	Agent 5	55	1,250.00	1,250.00
14	Agent 6	5	0.00	0.00
3a

Cell Formulas
Range	Formula
D3:D5	D3	=C3-N(C2)
C9:C14	C9	=SUMPRODUCT(--(B9>$A$3:$A$5),(B9-$A$3:$A$5),$D$3:$D$5)
D9:D14	D9	=(B9>5)*(B9-5)*25+(B9>55)*(B9-55)*25

 

[MARK858]

I thought the Named Ranges would show on the post but I did not check

Hopefully you just missed putting a check on the Named Range check box in XL2BB and there isn't an issue.