Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Excel Pivot Table Value Field Settings for Median

    I am hoping someone can help me out by devising a method for adding "Median" as a choice in the the Value Field Settings of a pivot table. I am fairly new to pivot tables so there may already be an option for this that I do not know about. Please help by providing any advice you may have if there is a way I am overlooking.

    Thank you

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    In the PivotTable Field List, click on the field in the Values area > Value Field settings.... > Summarize Values by tab > Average.

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    workingonit (2016-11-10)

  4. #3
    New Lounger
    Join Date
    Nov 2015
    Posts
    17
    Thanks
    8
    Thanked 2 Times in 2 Posts
    That is a known shortcoming of pivot tables. There is no option for and any workaround to get median. Average is mean not median.

  5. The Following User Says Thank You to _Zeno_ For This Useful Post:

    workingonit (2016-11-10)

  6. #4
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you for responding Maudibe

  7. #5
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you for responding Zeno. Thank you also for the information. I must say though, from a determined standpoint, I am kind of hoping you are wrong about the no work around part if you know what I mean.

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There is no workaround - you can't add functions to that dropdown, and there is no way to calculate what you need with a calculated field either. If you have Power Pivot it might be possible. Failing that, you'd have to use formulas outside (or instead of) your pivot table.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. The Following User Says Thank You to rory For This Useful Post:

    workingonit (2016-11-10)

  10. #7
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you for your response rory

Posting Permissions

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