Using QUARTILE in an Excel pivot table to summarise data by sub-populations
Asked Answered
P

4

21

I've got a large table of data in an Excel spreadsheet that, essentially, can be considered to be a collection of values for individuals identified as belonging to various subpopulations:

  IndivID   SubPopID  Value
     1          A       33.56
     2          E       42.31
     3          D       16.35
     4          A       50.59
     5          F       80.63
     ...

There are over 10,000 individuals in this table, and over 50 subpopulations.

I would like to calculate the 5-number summary (Min, Q1, Median, Q3, Max) for each of the subpopulations.

Min and Max are easy in a pivot table. But Q1, Q2 (median), and Q3 don't seem to be possible.

Question

Is there any way to calculate the quartiles for each subpopulation in a pivot table in Excel?

The answer should look like this:

SubPopID      Min     Q1     Q2        Q3      Max
   A         3.23    12.06   20.35   28.29     50.59
   B 
   C
   ...
Penguin answered 18/11, 2012 at 14:32 Comment(0)
C
21

My experience is that you can't do percentiles in a pivot table. I just did a web search and don't see anything to contradict that. I think the best you can do is have your source data in a table so you can refer to it dynamically, and then use the percentile function in another table.

I've seen one article about using PowerPivot, but that's only available in Excel 2010, and it looks hard. I have 2010, and when faced with a similar problem, I still opted to go the route I'm suggesting here.

EDIT: Explanation of my proposed alternative:

In answer to your question in the comments, here's how I'd calculate percentiles from a table of data:

Table of percentiles

It relies on an single formula, which is the same for every cell from F2 to H6. It's an array formula, meaning it's entered with Ctrl-Shift-Enter. It uses an IF statement to filter on the various possible SubPopIDs. Here's the formula for F2:

=PERCENTILE(IF(Table1[SubPopID]=F$1,Table1[Value],""),$E2/100)
Cornetist answered 18/11, 2012 at 16:51 Comment(5)
That was my fear. None of my web searches found anything of value for Excel 2007. For Excel 2010, however, the AGGREGATE( ) function looked quite promising, as it has a quartile option built-in. (Unfortunately, my customer does not use Excel2010 and has no plans to upgrade their organisation imminently). I'll keep this active for a couple of days, and hope that we're proved wrong and someone has found a clever workaround.Penguin
Doug, could you elaborate on what you mean by "source data in a table and percentile function in another table"? (Can't see how I could use Excel's 'QUARTILE()' function on a subset of rows, e.g. only on the SubPopID=A in the example). Thx.Penguin
Please see my edit. It relies on an array formula with an IF Statement.Cornetist
Brilliant! That works perfectly. BTW the screenshots and links were both instrumental in being able to replicate the approach. Many thanks!Penguin
I'm afraid that this filtered expression doesn't ignore empty values. It will assume a "" as zero which means that the results will be different. Try to find the min value and you will see.Flowerage
R
7

If you need to check a condition for more than one column (a=1 AND b=2) you can expand the array using the * character

=PERCENTILE((IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100)
Roomette answered 11/8, 2014 at 17:43 Comment(2)
Hi there. I think your formula might have a problem -- parentheses seem incorrectly placed.Penguin
yes this should be the code =PERCENTILE(IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100Stonecrop
O
1

I found this answer where a solution is described.

It uses the same aproach as described by Doug Glancy but associate it with a calculated field in the pivot table. The answer gives an example excel file.

Orlena answered 5/11, 2015 at 16:50 Comment(0)
M
1

Here's another approach using Measures:

  1. Create a PivotTable and select "Add this data to the Data Model"
    creating a pivot table with data model
  2. In PivotTabel Fields right-click on the table and select "Add Measure..."
    enter image description here
  3. Create a Measure using DAX formula: =PERCENTILE.INC(Table1[Score],0.85)
    enter image description here
  4. Add the newly created measure to the values of your PivotTable
    enter image description here enter image description here

Credit goes to @DougHExcel for this awesome idea! See his video for a demonstration of this approach: https://youtu.be/dD-oLbOLEBU?si=6FjVqvze8XhcS4rR&t=180

Mainis answered 31/8, 2023 at 8:36 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.