Hello! (first time, long time)
I am trying to establish a Commission Calculator in Excel. I have designed it in
four parts which are also four separate sheets: (1) the employee listing with hours worked (2) the data entry including date,
salesperson, customer name, amount, and program (3) my pivot table (which is the only way I was able to achieve a breakdown
by program of each salesperson and their deal per day) (4) the calculation of each salesperson's commission per deal on a per
diem basis. I am having trouble constructing the fourth part.
Basically, if a salesperson has x number of deals
per day, they get a flat dollar amount per deal; but if they have +x deals per day, they get a higher flat dollar amount per
So I am thinking that I need a GetPivotData formula that will retrive the pivot table's calculation of each
program, each salesperson, and their deal per day, but I don't want to hardcode the salesperson's name into the syntax. I
would like to reference the employee listing on the first sheet and have the formula search the pivot table for the name and
then return the deals per day.
Notes: This department has a high turn over that will create insane maintenance.
Salespeople also can move between programs. The Commission Rates are different for each program and are seasonal, so again, I
am reluctant to hardcode.
Is this even possible without a degree in computer programming? I have searched the
message boards, but have a hard time believing I have a unique question.
Thanks in advance,