Commission Calculating Formula HELP

borjasanz

New Member
Joined
Sep 17, 2013
Messages
5
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

Well-known Member
Joined
Aug 2, 2012
Messages
1,577
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,344
Office Version
365
Platform
Windows
Another option for lay out.
Excel Workbook
ABCD
1Goal$1,000
2# Reps. Reached goal:
3Sum
4
5
6Rep.AmountReached GoalComm. %
7SR1$1,100
8SR2$1,200TRUE0.24
9SR3$1,300TRUE0.26
10SR4$1,400TRUE0.28
11SR5$600FALSE0
Sheet
 

Forum statistics

Threads
1,081,691
Messages
5,360,644
Members
400,591
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top