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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,130
Messages
5,857,551
Members
431,885
Latest member
Rsdg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top