Median in Pivot Table in Excel 2010?

Astonishing that this functionality is not present in such an ancient application

Is there a known workaround?

I'm on about the part where you can change the aggregation type for a value field: It has sum, min, max, avg etc but not median

Solution 1:

For simple examples you can use array formulas instead of a PivotTable.

If you have the source data in rows 10:1000, category designations of the source data in column A, the source data values in column C, and the category being considered in G3, the following array formula will find the median:

{=MEDIAN(IF($A$10:$A$1000=G3,$C$10:$C$1000))}

Commit the entry with Ctrl+Shift+Enter, and copy down for the categories in G4, G5 etc

Solution 2:

Unfortunately, there is nothing built in to excel's pivot table function that will do this. You could try this add-on though. It claims to be able to do it, but I've never used it.

You could do the median work with the data and then include it in pivot table data, but at that point.. you know.. what's the point of the table..

Solution 3:

You can actually use the iserror function to exclude the total rows from medians. For example, if the Total row labels are in row A and the data you want the median of is in row I:

=MEDIAN(IF(ISERROR(FIND("Total",$A$5:$A$65535)),I5:I35535))

Solution 4:

The quickest and simplest way to get a median in your pivot tables is to import your Excel file into Google Sheets. There you can create a pivot table and use a median.