+ Reply to Thread
Results 1 to 3 of 3

How can I get the average of a pivot table column?

  1. #1
    Registered User
    Join Date
    06-14-2017
    Location
    New Haven, CT
    MS-Off Ver
    2013
    Posts
    11

    How can I get the average of a pivot table column?

    I want to find the average of a column in a pivot table. Essentially I want (Sum of Column values)/(Number of Rows). I know I can count the number of rows myself, but if the source data changes, the cells containing the total sum or the number of rows might change.

    So my question is, how do I link my formula to a pivot table column, rather than to specific cells such that when the source data changes, my formula does not get screwed up.
    Thanks!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: How can I get the average of a pivot table column?

    That really depends on your pivot table structure. But can't you just add avg of xxxx in value field?

    Few other methods...
    1. Use VBA to supply PivotTable.DataBodyRange.Column(index).Address to forumla.
    2. Use dynamic named range. Do note that if you have compact layout (as opposed to classic pivot layout), it can throw off calculation, depending on user interaction.
    3. Use OLAP model and CUBE function to get result.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    06-14-2017
    Location
    New Haven, CT
    MS-Off Ver
    2013
    Posts
    11

    Re: How can I get the average of a pivot table column?

    Alright, I used dyanamic name and range. What a nifty tool! Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 04-07-2017, 08:45 AM
  2. Pivot Table, average of sums in column
    By DeeRok in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-08-2014, 01:48 AM
  3. I want to add an average column to a pivot table
    By RobMcG in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-12-2013, 07:02 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1