Calculating Commissions

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
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
DEFGHIJKLMNOPQRSTUV
1StateCountyNeg.Sched.LastNameFirstNameDPMNameGD/SPDateRcvdCPPOCPPSPOPSC5CFDFHPHOCommissionPlanTotal
2CAmichaelKingXXXXX5
3CAmichaelKingXX2
4CAmichaelKingX1
5CAmichaelKingXXXXX5
6MTmichaelKingXX2
May 26-Jun 1st
cOMMISSION PROJECT.xls
XYZAAABACADAE
11PLAN2PLAN3PLANS4PLANS5PLANS6PLANS7PLANS
2EASY$8.00$11.00$15.00$18.00$22.00$23.00$25.00
3
4HARD$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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Active Member
Joined
Mar 30, 2004
Messages
280
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Try


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

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
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
DEFGHIJKLMNOPQRSTUVWXYZAAAB
1StateCountyNeg.Sched.LastNameFirstNameDPMNameGD/SPDateRcvdCPPOCPPSPOPSC5HPHOCommissionCPPOCPPSPOPSC5HPHO
2CAmichaelKingXXXXXEASY$3.50$3.50$4.50$4.50$9.00$2.50$2.50
3CAmichaelKingXX
4CAmichaelKingXHARD$5.50$5.50$6.50$6.50$11.00$2.50$2.50
5CAmichaelKingXXXXX
6MTmichaelKingXX
May 26-Jun 1st


As always, Thanks
msk7777
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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
DEFGHIJKLMNOPQRSTUVWXYZAAAB
1StateCountyNeg. Sched.Last NameFirst NameDPM NameGD/SPDate RcvdCPPOCPPSPOPSC5HPHOCommissionCPPOCPPSPOPSC5HPHO
2CAmichaelKingXXXXX25EASY$3.50$3.50$4.50$4.50$9.00$2.50$2.50
3CAmichaelKingXX7
4CAmichaelKingX3.5HARD$5.50$5.50$6.50$6.50$11.00$2.50$2.50
5CAmichaelKingXXXXX25
6MTmichaelKingXX11
Sheet1
 

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,388
Messages
5,547,661
Members
410,805
Latest member
Ginoji
Top