Hi Zack,

The sales by sales rep is located in a Hyperion database that is accessed by
Excel as soon as the books are closed at month-end. The Excel spreadsheet is
laid out as in the following example:-

Month: August 05

Salesrep Sales Commission

P $100000
X $300000
Y $200000
Z $150000

I need to calculate the required commission by salesrep from the following
table (for August 05 from the above example):-

Month Sales Commission

May-05 - -
May-05 $ 297,409 $600
May-05 $ 1,000,000 $833
Jun-05 - -
Jun-05 $ 330,515 $800
Jun-05 $ 1,500,000 $900
Jul-05 - -
Jul-05 $ 298,390 $500
Jul-05 $ 1,700,000 $ 1500
Aug-05 - -
Aug-05 $ 341,568 $400
Aug-05 $ 1,900,000 $600
Sep-05 - -
Sep-05 $ 319,376 $700
Sep-05 $ 2,000,000 $1500
Oct-05 - -
Oct-05 $ 379,096 $200
Oct-05 $ 1,000,000 $1800

I hope I have given you enough information this time, Zack.

Thanks,


Patrick

"Zack Barresse" wrote in message
...
Can you tell us where your data is located, giving us an idea of your data
structure? How about a 5-10 row example?

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Patrick Young" wrote in message
...
Hello Steve,

Every month, I have to calculate the commissions to be paid to a list of
sales reps, based on the value of their individual sales and the specific
commission program (different each month but the same for each sales rep
in
a given month) for the month. So the variables by sales rep are the
month,
the amount of the sales, the commission program (same for every sales
rep)
for the month. I want to copy the required function against each sales
rep
name to calculate the commission due to him.

The table below lists the criteria to be used to pay the commissions, and
they are ranked in ascending order of sales volume by month.

Thanks for your help,


Patrick

"STEVE BELL" wrote in message
news:K2i0f.348$zo6.339@trnddc05...
Patrick,

You might also be interested in the SumProduct worksheet function.
This one will look at values in one column and pull out only those
matching one criteria.
Than you can add additional columns to do the same. And than add the
last
column which is the value column (to add up). The result is the sum for
all the concurrent matches.

But again - we need details on how your columns are set up...

--
steveB

Remove "AYN" from email to respond
"Patrick Young" wrote in message
...


Please help me write the function that would match the Month and the
Sales to give me the amount of Commission for each combination of Month
and Sales Value.



Thanks,





Patrick




Month Sales Commission
May-05 - -
May-05 $ 297,409 $600
May-05 $ 1,000,000 $833
Jun-05 - -
Jun-05 $ 330,515 $800
Jun-05 $ 1,500,000 $900
Jul-05 - -
Jul-05 $ 298,390 $500
Jul-05 $ 1,700,000 $ 1500
Aug-05 - -
Aug-05 $ 341,568 $400
Aug-05 $ 1,900,000 $600
Sep-05 - -
Sep-05 $ 319,376 $700
Sep-05 $ 2,000,000 $1500
Oct-05 - -
Oct-05 $ 379,096 $200
Oct-05 $ 1,000,000 $1800