Hello,

Sorry I accidentally sent the first message without the details. Here
it is again with the appropriate details.

Regards, Adam

Dear all,

I am wondering if anyone might have some ideas on how to do the
following calculations on a large pivot table data set. I have tried
doing it through formulas in the pivot table, but because the formula
varies according to the name of the "company" field, it`s not
straightforward. If there is an ideal way using pivot tables, that
would be good to know. But if you think there is another more
appropriate way, I`d appreciate hearing it.

This is the original email with the details on the calculation. Thank
you for your suggestions.

Best regards,

Adam Nichols

original email:

Does anyone know how would be the best way to make calculations for
"profits" for each year on a very large data set which is structured as
follows:

year company division value
1999 pepsi marketing 100
1999 pepsi humanresources 200
1998 pepsi finance 232
2000 pepsi human resources 255
1990 nike clothes 22
1999 nike sports 1003
2000 nike advertising 220
1990 nike human resources 887
1999 pepsi advertising 8322
1995 pepsi marketing 23
1999 mcdonalds food 255
1999 mcdonalds restaurants 2299
1999 mcdonalds advertising 22
1998 mcdonalds food inspection 2343

where the calculation for "profits" varies according to the field called
"company". For example (to be done for each year):

If company = pepsi:
then calculation for "profits" is sum of all the values where "division"
= "marketing","humanresources"

If company = nike:
then calculation for "profits" is sume of the values where "division" =
"sports", "advertising","marketing"

If company = mcdonalds:
then calculation for "profits" is sum of all the values where "division"
= "food", "restaurants", "food inspection"

Is there an effective way to do this through a pivot table? I have
created a pivot table and tried to create the formulas there, but since
the the formula for "profit" is not the same throughout but differs
according to the name of the company, it was not clear to me how to do
this. I also tried creating multiple formulas for "profit", one for
each of the companies, but this has generated many useless blank lines
of calculation where the formula for one company was applied to other
companies for which it does not apply. Perhaps you know of a way to
remedy this.

Or is there a more ideal and efficient way to approach doing this, apart
from through calculations in a pivot table? I`d be very interested in
hearing your suggestions.

Thank you, and I look forward to hearing from you.

Best regards,

Adam Nichols





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

put the company in the pivot table Page field, the Year
in the Column Field and the division in the Row field.
Value goes into the Data field

select a company in the company field, then select those
divisions that you want in the rows field. The totals
will be automatic.

email me for my example

If you want code for this, then you will need a lookup
table to specify which divisions are needed for each
company.

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hello,

Sorry I accidentally sent the first message without the
details. Here
it is again with the appropriate details.

Regards, Adam

Dear all,

I am wondering if anyone might have some ideas on how to
do the
following calculations on a large pivot table data set.
I have tried
doing it through formulas in the pivot table, but
because the formula
varies according to the name of the "company" field,
it`s not
straightforward. If there is an ideal way using pivot
tables, that
would be good to know. But if you think there is
another more
appropriate way, I`d appreciate hearing it.

This is the original email with the details on the
calculation. Thank
you for your suggestions.

Best regards,

Adam Nichols

original email:

Does anyone know how would be the best way to make
calculations for
"profits" for each year on a very large data set which
is structured as
follows:

year company division value
1999 pepsi marketing 100
1999 pepsi humanresources 200
1998 pepsi finance 232
2000 pepsi human resources 255
1990 nike clothes 22
1999 nike sports 1003
2000 nike advertising 220
1990 nike human resources 887
1999 pepsi advertising 8322
1995 pepsi marketing 23
1999 mcdonalds food 255
1999 mcdonalds restaurants 2299
1999 mcdonalds advertising 22
1998 mcdonalds food inspection 2343

where the calculation for "profits" varies according to
the field called
"company". For example (to be done for each year):

If company = pepsi:
then calculation for "profits" is sum of all the values
where "division"
= "marketing","humanresources"

If company = nike:
then calculation for "profits" is sume of the values
where "division" =
"sports", "advertising","marketing"

If company = mcdonalds:
then calculation for "profits" is sum of all the values
where "division"
= "food", "restaurants", "food inspection"

Is there an effective way to do this through a pivot
table? I have
created a pivot table and tried to create the formulas
there, but since
the the formula for "profit" is not the same throughout
but differs
according to the name of the company, it was not clear
to me how to do
this. I also tried creating multiple formulas
for "profit", one for
each of the companies, but this has generated many
useless blank lines
of calculation where the formula for one company was
applied to other
companies for which it does not apply. Perhaps you know
of a way to
remedy this.

Or is there a more ideal and efficient way to approach
doing this, apart
from through calculations in a pivot table? I`d be very
interested in
hearing your suggestions.

Thank you, and I look forward to hearing from you.

Best regards,

Adam Nichols





*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!
.

It sounds like you need a single column that indicates if each row should be
considered in calculating profit. This column would use a formula that
references the company and the division for that, then looks these up in a
table to see if they should be considered.

You can build a master table with this information

concatenated_result company division flag
pepsiemarketing pepsie marketing yes
pepsiehuman resources pepsie human resources no


you can then use a formula like

=vlookup(B2&C2,Master_Table,4,False)

in the next available column in your data table.

you can then use a pivot table to see the profits by company.

Make the flag variable a page field and only use Yes in that field.

--
Regards,
Tom Ogilvy

"Adam Nichols" wrote in message
...
Hello,

Sorry I accidentally sent the first message without the details. Here
it is again with the appropriate details.

Regards, Adam

Dear all,

I am wondering if anyone might have some ideas on how to do the
following calculations on a large pivot table data set. I have tried
doing it through formulas in the pivot table, but because the formula
varies according to the name of the "company" field, it`s not
straightforward. If there is an ideal way using pivot tables, that
would be good to know. But if you think there is another more
appropriate way, I`d appreciate hearing it.

This is the original email with the details on the calculation. Thank
you for your suggestions.

Best regards,

Adam Nichols

original email:

Does anyone know how would be the best way to make calculations for
"profits" for each year on a very large data set which is structured as
follows:

year company division value
1999 pepsi marketing 100
1999 pepsi humanresources 200
1998 pepsi finance 232
2000 pepsi human resources 255
1990 nike clothes 22
1999 nike sports 1003
2000 nike advertising 220
1990 nike human resources 887
1999 pepsi advertising 8322
1995 pepsi marketing 23
1999 mcdonalds food 255
1999 mcdonalds restaurants 2299
1999 mcdonalds advertising 22
1998 mcdonalds food inspection 2343

where the calculation for "profits" varies according to the field called
"company". For example (to be done for each year):

If company = pepsi:
then calculation for "profits" is sum of all the values where "division"
= "marketing","humanresources"

If company = nike:
then calculation for "profits" is sume of the values where "division" =
"sports", "advertising","marketing"

If company = mcdonalds:
then calculation for "profits" is sum of all the values where "division"
= "food", "restaurants", "food inspection"

Is there an effective way to do this through a pivot table? I have
created a pivot table and tried to create the formulas there, but since
the the formula for "profit" is not the same throughout but differs
according to the name of the company, it was not clear to me how to do
this. I also tried creating multiple formulas for "profit", one for
each of the companies, but this has generated many useless blank lines
of calculation where the formula for one company was applied to other
companies for which it does not apply. Perhaps you know of a way to
remedy this.

Or is there a more ideal and efficient way to approach doing this, apart
from through calculations in a pivot table? I`d be very interested in
hearing your suggestions.

Thank you, and I look forward to hearing from you.

Best regards,

Adam Nichols





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!