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

 

[msk7777]

That doesn't work, if there is one "Hard State" is turns the whole sheet red. I was thinking it needed to be a VBA code to look down the State column and find a "Hard State" and then only change that row to red.

msk7777

 

[NBVC]

That doesn't work, if there is one "Hard State" is turns the whole sheet red. I was thinking it needed to be a VBA code to look down the State column and find a "Hard State" and then only change that row to red.

msk7777

Sorry, I made the cell absolute...should've been only the column

Try this instead:

=OR(ISNUMBER(SEARCH($D2,Hard)))

 

[msk7777]

I'm sorry to be so much trouble, but it's still not working. Its turning "Easy" state lines red also.

 

[NBVC]

Are you sure none of the states appear in both the hard and easy ranges?

Perhaps if there are blank rows you could have some wrong highlighting..

This should fix that problem...but otherwise I am not too sure what else could be causing it to pick up the "easy" states.

=AND($D2<>"",OR(ISNUMBER(SEARCH($D2,Hard))))


I am assuming your table starts at D2 and that column D contains the states, right?

 

[msk7777]

Ok, what I figured out what it's doing, it's turning the row above the "Hard" state row red and not the actual "Hard" state row. I can't see why though.

msk7777

 

[NBVC]

Ok, what I figured out what it's doing, it's turning the row above the "Hard" state row red and not the actual "Hard" state row. I can't see why though.

msk7777

It's probably because you selected from D1 instead of D2. Try again selecting from D2 to bottom right corner of your table.

The formula references the top most row in your selection and then it automatically adjusts for the remaining rows in your selection.

So if you enter a formula checking D2, then D2 should be within the top row in your selection before you apply the conditional formatting.

 

[msk7777]

Yep that was it, its fixed now, thanks so much for your help, sorry to give you such a hard time!

msk7777

 

[NBVC]

Not a problem at all. :wink: