Calculating Commissions Part 2 (tough one!)

Status
Not open for further replies.

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
Since my boss changed the variables I am really at a loss here on how to attempt to automate our commissions. Here is a sample of the spreadsheet that our recruiters use to enter their commissions. When they sign up someone with specific plans they just x-out the cell under the correct column. In the past we have just added up the totals manually. But now with growth there are too many recruiters to do this in a timely manner. I need to have Column S first look at Column D and determine if it is a Hard or Easy state (I already have them Named and defined). See here>>>
cOMMISSION PROJECT.xls
DEFGHIJKLMNOPQRS
1StateCountyNeg. Sched.Last NameFirst NameDPM NameGD/SPDate RcvdCPPOCPPSPOPSC5HPHOCommission
2CAmichaelKingXXXXX
3CAmichaelKingXX
4CAmichaelKingX
5CAmichaelKingXXXXX
6MTmichaelKingXX
7MTmichaelKingXXX
May 26-Jun 1st

Then I need it to match the same X'd-out plans that I have defined on a different tab (if needed I could place it on the same worksheet) in Columns C-I and take the assigned commission from either Column J or K (these columns go down to line 99).

[HTML removed by admin]
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
Option 2

Ok here is another option that I know is a lot easier to work with than my first, instead of having 98 different plan combinations (which is our current commission structure) I have asked that next year we change to a different structure to work with so there are less variables. On the same sheet I added the following where Column S can grab the correct correct commission from. If this is easier and anyone can help me with a formula that would match the correct commission total, that would help me with my proposal. I believe that with the above current commission structure there are too many variables for Excel to automate this process. Also, if anyone has a suggestion to format things differently so that it will work then feel free.
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
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,673
Messages
5,549,355
Members
410,910
Latest member
DessertDiva
Top