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!