Commission Calculating Formula HELP

[borjasanz]

Hey guys.

I'm having a lot of trouble with a formula to calculate commissions for my sales team.

This is how it works, we have five sales representatives in the team:

Sales Rep1
Sales Rep2
Sales Rep3
Sales Rep4
Sales Rep5

Ok. So each one of them has a minimum sales goal monthly. IF they reach it, then they enter the commissions pool. IF they don't, their sales enter the pool but they cannot get a commission.

For example:

If all of them reach their goal, then each gets their % of the total sales.
Imagine each of them sold $1,000. Then each would get 20% of the commission available (since they are 5)

However, if only 4 of them reach their goal, the fifth person (who did not reach it) would not get a commission.
Imagine the goal is $1000

SR1 sold $1,100
SR2 sold $1,200
SR3 sold $1,300
SR4 sold $1,400
SR5 sold $600.

Total: $5,600

In this case, SR5 does not get a commission (0%) because he didn't reach the sales minimum.

Therefore, SR1 has 22% of commissions (1,100/5000), SR2 has 24% (1200/5000), SR3 has 26% (1300/5000) and SR4 has 28% (1400/5000)

The problem is, we don't know who of the representatives will reach their goal. Therefore, I had to create a cumbersome formula to take into account every possibility that exists.
Possibility 1: All of them reach their goal. (5)
Possibility 2: SR1,2,3,4 reach their goal. (4)
Possibility 3: SR1,2,3,5 reach their goal. (4)
Possibility 4: SR1,2,4,5 reach their goal. (4)
Possibility 5: SR1,3,4,5 reach their goal. (4)
Possibility 6: SR1,2,3 reach their goal. (3)
Possibility 7: SR1,2,4 reach their goal (3)
Possibility 8: SR1,2,5 reach their goal (3)
Possibility 9: SR1,3,5 reach their goal. (3)
Possibility 10: SR1,3,4 reach their goal (3)
Possibility 11: SR1,4,5 reach their goal (3)
Possibility 12: SR1,2 reach their goal (2)
Possibility 13: SR1,3 reach their goal (2)
Possibility 14: SR1,4 reach their goal (2)
Possibility 15: SR1,5 reach their goal (2)
Possibility 16: SR1 reaches his goal (1)
Etc.

This is ONLY the first sales rep formula. You can imagine how tiresome this is to formulate. I need a way that is scalable (we might get 6 reps soon) and also more efficient. This is just impossible since I have to replicate this and cannot copy the formulas from one cell to another.
This works for now, but our business is growing fast and it's just not efficient and makes me lose a lot of time because I have to change the formula each time we get a new sales rep.

For example: When we went from 4 to 5, I had the following possibilities before:
SR1,SR2,SR3,SR4 reach their goal.
1,2,3 reach
1,2,4 reach
1,3,4 reach
1,2 reach
1,3 reach
1,4 reach
1 reach

This is a ridiculous IF formula with a lot of other IFs nested. Something like:

=if(and(SR1=reach,SR2 = reach, SR3 = reach, SR4 = reach), salesSR1/(salesSR1+salesSR2+salesSR3+salesSR4),if(and(SR1 = reach,SR2 = reach, SR3 = reach),salesSR1/(salesSR1+salesSR2+salesSR3),if(and(SR1 = reach,SR2 = reach, SR4 = reach),salesSR1/(salesSR1+salesSR2+salesSR4),if(and(SR1 = reach,SR3 = reach, SR4 = reach),salesSR1/(salesSR1+salesSR3+salesSR4),if(and(SR1 = reach,SR2 =reach), salesSR1/(salesSR1+salesSR2),if(and(SR1 = reach,SR3 =reach), salesSR1/(salesSR1+salesSR3),if(and(SR1 = reach,SR4 =reach), salesSR1/(salesSR1+salesSR4),if(SR1=reach,100%,0))))))))

This is ridiculous. Imagine expanding this to one more sales rep. And the worst is that every formula has to be different for each sales rep, since the possibilities are also different.

I REALLY NEED SOME HELP

What do you guys have in mind?

 

[pjmorris]

I would offer this:

=IF(D13>=$F$10,(D13+SUMIF($D$13:$D$17,"<"&$F$10)/COUNTIF($D$13:$D$17,">="&$F$10))/SUM($D$13:$D$17),0)

This will calculate the percentage commission for the figure in cell D13. It assumes your sales are in cells D13:D16 and in F10 is the minimum figure. If you have more sales people simply expand the D13:D17 range as necessary and then copy the formula down the column in which you want the results.

A slightly simpler formula that doesn't include the sales of those under the minimum is this: =IF(D13>=$F$10,D13/SUMIF($D$13:$D$17,">="&$F$10),0)

But I don't think that that is an accurate reflection of your requirement at

IF they don't, their sales enter the pool but they cannot get a commission.

Though I don't think it makes a huge difference.

Hope this helps.

 

[AhoyNC]

Another option for lay out.

Excel Workbook
	A	B	C	D
1	Goal	$1,000
2	# Reps. Reached goal:	4
3	Sum	$5,000
4
5
6	Rep.	Amount	Reached Goal	Comm. %
7	SR1	$1,100	TRUE	0.22
8	SR2	$1,200	TRUE	0.24
9	SR3	$1,300	TRUE	0.26
10	SR4	$1,400	TRUE	0.28
11	SR5	$600	FALSE	0
Sheet