locked
Is there a way to calculate 95th percentile in a pivot table? RRS feed

  • Question

  • I am trying to calculate summary stats for a large database of environmental data. We need Min, Max, Mean, Median, 95th Percentile, and standard deviation. Unfortunately, median and 95th percentile are not built in functions into a pivot table. Is there any way around this? I have been calculating it manually, but its tedious. 

    Thanks so much!

    Wednesday, December 20, 2017 1:59 PM

All replies

  • 1. right click on pivot table, then choose "Value fields settings"

    2. just choose second tab "Show values as"

    3. play with the settings


    or you can set up a artificial calculated field in pivot:

    1. left click anywhere in pivot

    2. on ribbon for pivots click "Formula"

    3. set up formula that shows you additional column with your formula, like

    =dataset*0,5

    MY BOOK

    Thursday, December 21, 2017 1:12 PM
  • Thanks for the reply. 

    Unfortunately I am not seeing a percentile function. I see show data as "% of total" and 'Incdex" but I do not see how I can get percentiles. 

    For your second suggestion, i do not see a formula function. Please advise. 

    Thursday, December 21, 2017 4:06 PM
  • Try it this way.  I wasn't looking at any pivots when I posted that...


    MY BOOK

    Thursday, December 21, 2017 4:31 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    PivotTable with Median and 95th Percentile.
    http://www.mediafire.com/file/15oyb7utrc06zau/12_21_17.xlsx
    http://www.mediafire.com/file/bo53io34zhyn16i/12_21_17.pdf

    • Proposed as answer by Chenchen Li Friday, December 22, 2017 7:36 AM
    Thursday, December 21, 2017 6:40 PM