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

[tmk40126]

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

 

[PaddyD]

see the progressive pricing example:

http://www.cpearson.com/excel/pricing.htm

 

[barry houdini]

This should be possible with a formula, rather than VBA.

See the following:

Book1
	A	B	C	D	E	F	G
1	Agent	Income	Commission	From	To
2	Joe	$47,050	$29,184.85	$0	$10,000	55%
3	Elle	$11,350	$6,309.95	$10,001	$15,000	60%
4	Jack	$78,111	$52,488.55	$15,001	$20,000	60%
5	Jill	$128,000	$94,299.70	$20,001	$25,000	60%
6				$25,001	$30,000	65%
7				$30,001	$35,000	65%
8				$35,001	$40,000	65%
9				$40,001	$45,000	70%
10				$45,001	$50,000	70%
11				$50,001	$55,000	70%
12				$55,001	$60,000	75%
13				$60,001	$65,000	75%
14				$65,001	$70,000	75%
15				$70,001	$75,000	80%
16				$75,001	$80,000	80%
17				$80,001	$85,000	80%
18				$85,001	$90,000	80%
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]

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!