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
...
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