# Help With Commission Rates (not sure if posted last time!)

#### tmk40126

##### New Member
I am attempting to use the following "stepped" commission table to compute various income scenarios for my business and having a REALLY tough time!

I have the following...

1) A staff of (30) agents who have earned various commission amounts. Example - (names in A1 to A4, incomes in B1 to B4, need commissions payable for each agent posted to C1 to C4):

A B C
1 Joe \$47,050
2 Elle \$11,350
3 Jack \$78,111
4 Jill \$128,000

AND..

2) A commssion structure that could increment in as little as \$5k increments but may not (i.e. I don't know yet exactly where the breaks are going to be - only that the will occur at no less than 5K increments)

Example of a possible rate structure (income ranges in D1 to E18 and corresponding commission rates in F1 to F18)

D E F
1 \$0 \$10,000 55%
2 \$10,001 \$15,000 60%
3 \$15,001 \$20,000 60%
4 \$20,001 \$25,000 60%
5 \$25,001 \$30,000 65%
6 \$30,001 \$35,000 65%
7 \$35,001 \$40,000 65%
8 \$40,001 \$45,000 70%
9 \$45,001 \$50,000 70%
10 \$50,001 \$55,000 70%
11 \$55,001 \$60,000 75%
12 \$60,001 \$65,000 75%
13 \$65,001 \$70,000 75%
14 \$70,001 \$75,000 80%
15 \$75,001 \$80,000 80%
16 \$80,001 \$85,000 80%
17 \$85,001 \$90,000 80%
18 \$90,001 \$90,001+ 85%

I need to do What-If scenarios where I plug in various agent earnings into B1 to B4 and varying commission amounts into F1 to F18 and the total commission amount, based on the changing/incrementing chart above, is returned for each agent in C1 to C4.

HELP! I don't know VBA. I will gladly pay for help if there is anyone out there that can provide a solution!!

Bless you.

Lee from Hawaii

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
This should be possible with a formula, rather than VBA.

See the following:
Book1
ABCDEFG
1AgentIncomeCommissionFromTo
2Joe\$47,050\$29,184.85\$0\$10,00055%
3Elle\$11,350\$6,309.95\$10,001\$15,00060%
4Jack\$78,111\$52,488.55\$15,001\$20,00060%
5Jill\$128,000\$94,299.70\$20,001\$25,00060%
6\$25,001\$30,00065%
7\$30,001\$35,00065%
8\$35,001\$40,00065%
9\$40,001\$45,00070%
10\$45,001\$50,00070%
11\$50,001\$55,00070%
12\$55,001\$60,00075%
13\$60,001\$65,00075%
14\$65,001\$70,00075%
15\$70,001\$75,00080%
16\$75,001\$80,00080%
17\$80,001\$85,00080%
18\$85,001\$90,00080%
19\$90,001\$90,001+85%
20
Sheet2

Note: I have put in a header row, F1 needs to be empty or zero

Values in column E are not used - commission bands are assumed to be contiguous, i.e. there are no gaps beteen them.

Formula in C2 copied down column

=SUMPRODUCT(--(B2>\$D\$2:\$D\$19),B2-\$D\$2:\$D\$19,\$F\$2:\$F\$19-\$F\$1:\$F\$18)

This formula is based on the method explained here

http://www.mcgimpsey.com/excel/variablerate.html

Barry Houdini - Can't tell you how much I appreciate you taking the time to construct your response. How can I thank you? If you are a coffee drinker send me your mailing address and I will post some of our famous Kona Coffee to you. Otherwise, name your reward.

You guys are great!

Replies
0
Views
131
Replies
2
Views
875
Replies
2
Views
348
Replies
2
Views
753
Replies
3
Views
488

1,196,318
Messages
6,014,614
Members
441,832
Latest member
tony tessman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back