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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Pivot Table: Calculating a product of two columns?
- Thread starter donc
- Start date
RoryA
MrExcel MVP, Moderator
- Joined
- May 2, 2008
- Messages
- 43,242
- Office Version
- 365
- Platform
- Windows
- MacOS
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.
Sum(fieldA * fieldB)
you will get:
Sum(fieldA) * sum(FieldB)
For detail rows, it will be correct though.
Upvote
0
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
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
Code:
SELECT Quote*LocationCount AS [New Name]
Upvote
0
Post script. Like this recent thread http://www.mrexcel.com/forum/showthread.php?t=384527 F
Upvote
0
Similar threads
- Question
