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!