Results 1 to 4 of 4

Pivot Table: Calculating a product of two columns?

This is a discussion on Pivot Table: Calculating a product of two columns? within the Excel Questions forums, part of the Question Forums category; Can I do something with field settings to calculate the product of two cells in the same row of data? ...

  1. #1
    New Member
    Join Date
    Aug 2008
    Posts
    16

    Default Pivot Table: Calculating a product of two columns?

    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?

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    22,457

    Default Re: Pivot Table: Calculating a product of two columns?

    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.

  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,851

    Default Re: Pivot Table: Calculating a product of two columns?

    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

    Code:
    SELECT Quote*LocationCount AS [New Name]

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,851

    Default Re: Pivot Table: Calculating a product of two columns?

    Post script. Like this recent thread PivotTable and Access Formulas F

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com