Function Vlookup, Match or Index?

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

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.

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


"Patrick Young" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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
>
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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
>
>
>
>
>

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


"Zack Barresse" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
> "Patrick Young" <[email protected]> wrote in message
> news:[email protected]...
>>
>>
>> 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

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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>>
>>
>> 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



>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>>
>>> 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
>
>
>
>>>
>

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

[email protected]
Remove "AYN" from email to respond
--
steveB

Remove "AYN" from email to respond
"Patrick Young" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Zack Barresse" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>
>>
>> "Patrick Young" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>>
>>> 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
>
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>
>>>>
>>>> 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
>>
>>
>>
>>>>
>>
>
>