Pivot Table: Calculating a product of two columns?

[donc]

Can I do something with field settings to calculate the product of two cells in the same row of data? I.e. I want my quotes column to produce the product of the the same quotes column and the location counts column in the same row of data...Am I thinking correctly that I should/may be able to do that with field settings?

 

[RoryA]

It depends on your table. YOu can use calcualted fields to multiply one field by another, but it will work at an aggregate level for any subtotals, in the sense that instead of getting:
Sum(fieldA * fieldB)
you will get:
Sum(fieldA) * sum(FieldB)

For detail rows, it will be correct though.

 

[Fazza]

Hi,

Save the data file and (for simplicity) give the data and headers a defined name, not dynamic. Then from a new workbook to create the pivot table and at the first step of the wizard take the external data option. Follow the wizard and at the end take the option to edit in MS Query you can edit the SQL there - hit the SQL button - and add the calculated field there. SQL like below. Exit & complete pivot table. Move the resultant sheet back into the source file if you like. (It is created in a new file to avoid memory leak problems.)

HTH, Fazza

SELECT Quote*LocationCount AS [New Name]

 

[Fazza]

Post script. Like this recent thread http://www.mrexcel.com/forum/showthread.php?t=384527 F