Calculating Commissions

[msk7777]

I have an issue I honestly have no idea where to begin and wanted to see if anyone could help me. Below is an example of the spreadsheets that recruiters have to use each week to enter their contracts. I have one column on the side that Countif's how many plans the recruiter signed the doctor up for, then I have a commission structure in additional columns that have what the commissions are for the certain number of plans. I would like for Excel to automatically enter the commission total but have no idea how to go about this. One problem I also face is commissions are different for hard or easy states (hard states:AL, AK, AR, DC, DE, HI, ID, IA, IN, KS, KY, LA, ME, MN, MS, MT, NE, NH, NM, NC, ND, OK, RI, SC, SD, VT, WV, WI, WY>>>>>>easy states: AZ, CA, CO, FL, GA, IL, MD, MA, MI, MO, NV, NJ, NY, OH, OR, PA, TN, TX, UT, VA, WA). If someone could help me I would appreciate it!

cOMMISSION PROJECT.xls
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	State	County	Neg.Sched.	LastName	FirstName	DPMName	GD/SP	DateRcvd	CPPO	CPPS	PO	PS	C5	CF	DF	HP	HO	Commission	PlanTotal
2	CA			michael	King				X	X	X	X	X						5
3	CA			michael	King				X	X									2
4	CA			michael	King				X										1
5	CA			michael	King				X	X	X	X	X						5
6	MT			michael	King				X	X									2
May 26-Jun 1st

cOMMISSION PROJECT.xls
	X	Y	Z	AA	AB	AC	AD	AE
1		1PLAN	2PLAN	3PLANS	4PLANS	5PLANS	6PLANS	7PLANS
2	EASY	$8.00	$11.00	$15.00	$18.00	$22.00	$23.00	$25.00
3
4	HARD	$13.00	$16.00	$20.00	$23.00	$27.00	$28.00	$30.00
May 26-Jun 1st

also, these two examples are from the same worksheet, thanks again!

Msk7777

 

[just_jon]

OK, do this --

1] create, on some new sheet, 2 lists - one each down a column for HARD states and another for EASY states.

2] using Insert | Name | Define, name these 2 lists EASY and HARD

Now, in U2 copied down:

=INDEX($Y$2:$AE$4,1+ISNA(MATCH($D2,EASY,0))*2,MATCH($V2 &"PLANS",$Y2:$AE2,0))

Or

=INDEX($Y$2:$AE$4,3-ISNA(MATCH($D2,HARD,0))*2,MATCH($V2 &"PLANS",$Y2:$AE2,0))

 

[NBVC]

Name your two lists Easy and Hard, respectively...

then use this:

=INDEX($Y$2:$AE$4,MATCH(IF(ISNUMBER(MATCH($D2,Easy,0)),"EASY","HARD"),$X$2:$X$4,0),MATCH($V2,LEFT($Y$1:$AE$1,1)+0,0))

Confirmed with CTRL+SHIFT+ENTER, not just ENTER and copy down.

 

[msk7777]

Thank you so much! That worked great! The only issue is that I noticed if I "X" out all nine plans it goes back to "N/A". I don't know how to adjust it so that if the Column V has 7 or more plans it equals the specified commission? Thanks again!

 

[just_jon]

Try


INDEX($Y$2:$AE$4,1+ISNA(MATCH($D2,EASY,0))*2,MATCH($V2 &"PLANS",$Y2:$AE2))

 

[msk7777]

New twist

Ok my boss threw in a twist that I need advice on, he wants to change the variables where the above formulas won't work, instead of paying our commissions as 1 plan = $$$$$, 2 plans=$$$$, etc., he wants to pay each plan by a certain amount. Is it possible to create a formula that will say if Cell L2 has an X then it will match it up with Cell V2, and so on with each row in Columns L-R? See below for examples

cOMMISSION PROJECT.xls
	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
1	State	County	Neg.Sched.	LastName	FirstName	DPMName	GD/SP	DateRcvd	CPPO	CPPS	PO	PS	C5	HP	HO	Commission			CPPO	CPPS	PO	PS	C5	HP	HO
2	CA			michael	King				X	X	X	X	X					EASY	$3.50	$3.50	$4.50	$4.50	$9.00	$2.50	$2.50
3	CA			michael	King				X	X
4	CA			michael	King				X									HARD	$5.50	$5.50	$6.50	$6.50	$11.00	$2.50	$2.50
5	CA			michael	King				X	X	X	X	X
6	MT			michael	King				X	X
May 26-Jun 1st

As always, Thanks
msk7777

 

[NBVC]

You can try this formula in S2 copied down:

=IF(ISNUMBER(MATCH(D2,Easy,0)),SUMPRODUCT(($L2:$R2="X")*(ISNUMBER(MATCH($L$1:$R$1,$V$1:$AB$1,0)))*$V$2:$AB$2),SUMPRODUCT(($L2:$R2="X")*(ISNUMBER(MATCH($L$1:$R$1,$V$1:$AB$1,0)))*$V$4:$AB$4))

This assumes that if the state is not in the "Easy" range, then it is considered "Hard".

Book3 Sep26.xls
	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
1	State	County	Neg. Sched.	Last Name	First Name	DPM Name	GD/SP	Date Rcvd	CPPO	CPPS	PO	PS	C5	HP	HO	Commission			CPPO	CPPS	PO	PS	C5	HP	HO
2	CA			michael	King				X	X	X	X	X			25		EASY	$3.50	$3.50	$4.50	$4.50	$9.00	$2.50	$2.50
3	CA			michael	King				X	X						7
4	CA			michael	King				X							3.5		HARD	$5.50	$5.50	$6.50	$6.50	$11.00	$2.50	$2.50
5	CA			michael	King				X	X	X	X	X			25
6	MT			michael	King				X	X						11
Sheet1

 

[msk7777]

That worked! Thank you so much!

 

[msk7777]

VBA

using the same "Hard State", "Easy State" criteria, is their a code that I can input that will change any "Hard State" row to red automatically?

msk7777

 

[NBVC]

Re: VBA

using the same "Hard State", "Easy State" criteria, is their a code that I can input that will change any "Hard State" row to red automatically?

msk7777

Use Conditional Formatting:

Select the entire table

Go to Format|Conditional Formatting

Select Formula Is from 1st drop down

Enter this formula =OR(ISNUMBER(SEARCH($D$2,Hard)))

Click Format and select your colour scheme(s)

Click Ok

Click Ok.