Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    adding simple math functions to pivot table (xp & 2003)

    I would like to add a simple math functions to pivot table, such as an avg.

    could this be accomplished in the layout of the pivot table or is it mandatory to be done after getting the table.

    I will add a sample work file.

    thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    You can add a formula field in the pivot table after you have created it:
    - I'd drag the data button to the top right so that Sum of FREQ and Sum of Cost are displayed next to each other.
    - Click anywhere in the data area of the pivot table.
    - Select Pivot Table | Formulas | Formula Field... from the Pivot Table toolbar.
    - Enter (for example) AvgOfCost as name, and =COST/FREQ as formula.
    - Click Insert Field, then click OK.
    - You'll see a new column. Click anywhere in it.
    - Select Pivot Table | Field Settings... from the Pivot Table toolbar.
    - Specify a caption for the field, e.g. Avg of Cost.
    - Set the number format.
    - Click OK.
    See the attached version.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    I'm sorry Hans, I guess I have a mental block here. In step three i cannot find Formula Field from the pivot table tool bar. The options given are 4 and Calculated item is grayed out. the remaining items are Calculated Field, Solve Order & List Formulas. There is a Field settings from the Pivot Table button and this has an average function. So at this juncture I'm lost. i cannot find that formula field. Could you specify? Thanks

    I am attaching w/b where i am at.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    Sorry, I meant Calculated Field instead of Formula Field.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    Hans,
    This works great but the title on the pivot table for Avg of Cost on yours becomes Sum of Avg of Cost on my Pivot Table.

    Any ideas why this happens.

    Thanks Again

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    One other item. could I get a percent from each total line item to the Grand total. or each amount to the respective total line?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    You can change the display name of the calculated field by selecting Pivot Table | Field Settings from the Pivot Table tool bar.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    Once again, click anywhere in the relevant data.
    Select Pivot Table | Field Settings... from the Pivot Table tool bar.
    Click Options >>
    Select the option that you want from the Show Data As dropdown - among the choices are % of row, % of column and % of total.

  9. #9
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: adding simple math functions to pivot table (xp & 2003)

    Thank you

Posting Permissions

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