# Calculating commissions with tiers, by month, AND across multiple people.

#### excellinginseattle

##### New Member
My question is related to calculating sales commissions.

Here's my problem: determining a commission rate is based on what has been closed in a territory, but calculating the actual commission earned is based off what a person himself closed. I know how to do this with a ton of nested if statements, but there must be a better way?

For example, we have a territory called West 1

West 1 has the following milestones:
Milestone 1: \$500,000
Milestone 2: \$1,000,000
Quota: \$1,500,000
Stretch Quota: \$2,000,000

Commission rates are:
2% up to Milestone 1
10% up to Milestone 2
20% up to Quota
25% up to Stretch
20% above Stretch

Let's say there were 2 people that closed business in the territory

 Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec Person 1 200,000 200,000 Person 2 300,000

<tbody>
</tbody>

I need to be able to calculate that Person 2 earns 2% on \$100,00 and 10% on \$200,000.

Calculations need to be cumulative each month.

Can someone help?

Thanks!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### AlanY

##### Well-known Member
based on your example let say Person 1 also closed 300,000 in March, what is the commission for both of them?
i.e. who is the lucky guy to get the 2%?

#### excellinginseattle

##### New Member
Good question. Person 1 would get the 2% because person 1 was the first territory owner. He left, and person 2 arrived. There is never overlap - it's time based.

#### miless2111s

##### Active Member
This is a rather long winded "show your workings" sort of way but I'm sure it can be condensed if it's doing the right sort of things

 A B C D E F G H 1 Month Jan Feb March April May 2 P1 200 200 3 P2 300 500 4 200 400 700 700 1200 SUM(F2:F3)+E4 5 Tier 2% 2% 10% 10% 20% VLOOKUP(F4,rewards,2) 6 Possible split FALSE FALSE TRUE FALSE TRUE F5<>E5 7 Threshold 500 500 1000 VLOOKUP(F4,rewards,1,TRUE) 8 Amount in upper rate 200 200 IF(F6,F4-F7,"") 9 Amount in lower rate 100 300 IF(F6,F3-F8,"") 10 Upper rate 10% 20% IF(F6,F5,"") 11 Lower Rate 2% 10% IF(F6,E5,"") 12 13 Earnings 4 4 22 70 IF(F6,(F11*F9)+(F10*F8),F5*SUM(F2:F3))

<tbody>
</tbody>

The reward structure is:

 - 2% 500.00 10% 1,000.00 20% 1,500.00 25% 2,000.00 20%

<tbody>
</tbody>

Hope that helps

Last edited:

Thank you!

#### excellinginseattle

##### New Member
I'm still having issues.

I think I've come up with two methods that are very close to working, but I need help.

Any experts out there who can help me with either a min(max()) or sumproduct solution? I think both could be very simple and efficient.

#### excellinginseattle

##### New Member
I still haven't found a solution that works and am really struggling. Here is a screen shot of what I referenced in the link above. If anyone could help, I'd really appreciate it.

-- removed inline image ---

Formula in X12: =F12*\$P\$3-(\$P\$3-\$Q\$3)*MIN(MAX(0,SUM(\$J12:L12)-(L12-F12)-\$Q12),F12)
Formula in X13: =F13*\$P\$3-(\$P\$3-\$Q\$3)*MIN(MAX(0,SUM(\$J13:L13)-\$Q13),F13)
Formula in X19: =SUMPRODUCT((L19 >= \$P19:\$T19)*(L19 - \$P19:\$T19)*(\$P\$3:\$T\$3))

Ideas? Is what I'm trying to do even possible without a load of if statements?

Thanks!

Last edited:

#### excellinginseattle

##### New Member
Complex commission calculations - multiple people and tiers.

I'm sorry for the mess above...here is a cleaned up post:

I still haven't found a solution that works and am really struggling. Here is a mock up of what I referenced in the link above. If anyone could help, I'd really appreciate it.

COMMISSION RATES ARE BASED ON WHAT HAS BEEN CLOSED IN THE TERRITORY AT THE TIME A PERSON'S COMMISSIONS ARE CALCULATED
THE RATE IS THEN APPLIED TO WHO CLOSED WHAT

Method 1: Min(Max())
Issue 1: how do I modify the commission calculations to include all the milestones?
Issue 2: how do I modify the commission calculations so that only person 1's calculations don't include what person 2 closed but person 2's calculations do. See difference in cell Y6 vs. Y7.
Issue 3: how do I modify person 1's commission calculations so they don't show up after he is no longer closing business?

Method 2: Sumproduct - I have this working for 1 person…just not 2
Issue 1: how do I make sumproduct take into account what's been closed in the territory (which is important when a milestone/rate changes) but calculate on what a person has closed?
Issue 2: how do I handle negative values in a month?

Formula in Y6: =G6*\$Q\$3-(\$Q\$3-\$R\$3)*MIN(MAX(0,SUM(\$K6:M6)-(M6-G6)-\$R6),G6)
Formula in Y7:=G7*\$Q\$3-(\$Q\$3-\$R\$3)*MIN(MAX(0,SUM(\$K7:M7)-\$R7),G7)
Formula in Y10:=SUMPRODUCT((M10 >= \$Q10:\$U10)*(M10 - \$Q10:\$U10)*(\$Q\$3:\$U\$3))

Ideas? Is what I'm trying to do even possible without a load of if statements?

Thanks!

 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB 2 3 Who Closed What Territory Totals Rates 2% 10% 20% 25% 20% Commission Calcs 4 Jan Feb Mar Apr May Jan Feb Mar Apr May Milestone1 Milestone 2 Quota Stretch Jan Feb Mar Apr May 5 Method 1 6 Territory 1 Person 1 200,000 200,000 300,000 200,000 200,000 600,000 -5,000 350,000 500,000 1,000,000 1,500,000 2,000,000 4,000 4,000 22,000 40,000 39,600 Z6 and AA6 should not be populated 7 Territory 1 Person 2 300,000 -5,000 350,000 200,000 200,000 600,000 -5,000 350,000 500,000 1,000,000 1,500,000 2,000,000 30,000 -500 35,000 Should be \$69,000 (\$345k@20% and \$5k@10%) 8 9 Method 2 10 Territory 1 Person 1 200,000 200,000 300,000 200,000 200,000 600,000 -5,000 350,000 500,000 1,000,000 1,500,000 2,000,000 4,000 4,000 22,000 11 Territory 1 Person 2 300,000 -5,000 350,000 200,000 200,000 600,000 -5,000 350,000 500,000 1,000,000 1,500,000 2,000,000 4,000 4,000 22,000 0

<tbody>
</tbody>

#### miless2111s

##### Active Member
Re: Complex commission calculations - multiple people and tiers.

Do you have a situation where P1 and P2 are active in the same month, i.e. you need to split the month by some fraction between them? This is suggested by the 300K in March which was only P2 in the original example.

Replies
8
Views
467
Replies
3
Views
597
Replies
1
Views
438
Replies
4
Views
351
Replies
2
Views
243

1,118,167
Messages
5,570,591
Members
412,328
Latest member
Mrcaff88