Function Vlookup, Match or Index?

[Patrick Young]

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

 

[Zack Barresse]

Hello Patrick,

It would help if you explained a little more. Right now we only have what
information you are attempting to use to return a value. This does not tell
us how your data is structured, which doesn't really allow us to give you a
working solution. Try explaining in greater detail how your data is setup,
where you are wanting to put this formula and what the desired results
should be.

 

[STEVE BELL]

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...

 

[Patrick Young]

Hello Zack,

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.

Thanks for your help,


Patrick

Hello Patrick,

It would help if you explained a little more. Right now we only have what
information you are attempting to use to return a value. This does not
tell
us how your data is structured, which doesn't really allow us to give you
a
working solution. Try explaining in greater detail how your data is
setup,
where you are wanting to put this formula and what the desired results
should be.

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

 

[Patrick Young]

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

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...

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

 

[Zack Barresse]

Can you tell us where your data is located, giving us an idea of your data
structure? How about a 5-10 row example?

 

[STEVE BELL]

Patrick,

One way to do this is with Data >> SubTotals

If you are not familiar with this - it goes
through the table and adds in a subtotal at each change in a designate
field. (I choose Month).

After it is done you have an outline that you can collapse to just show the
subtotals.

Play with this and than you can record a macro to automate it.

I sent you a sample workbook with your sample data.

See if this is what you are looking for...

(e-mail address removed)
Remove "AYN" from email to respond

 

[Patrick Young]

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