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

tmk40126

New Member
Joined
Dec 4, 2005
Messages
2
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

tmk40126

New Member
Joined
Dec 4, 2005
Messages
2
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.

Also, thanks to you too PaddyD for the link!

You guys are great!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,621
Messages
5,832,735
Members
430,160
Latest member
a_majda

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top