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?