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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,136
Messages
5,576,295
Members
412,716
Latest member
Ardin
Top