Unfortunately, pivot tables do not have the functionality to compute median values. However, there is a workaround. You can create what is effectively a “pivot” table of medians by using a powerful Excel feature called the array.

I will take you through the steps here. In the example below, we have a list of employees along with their times for completing a set of tasks. The two tables (median vs. average) look at the typical times required by the employees to complete their tasks.median_pivot_v._avg_pivot

The “average” table was created with a pivot table. The “median” table was created by doing the following:

1) Create a column with the nine possible “employees” options.

2) In the cell to the right “Edgar” type the following: =MEDIAN(IF($A$2:$A$500=D3,$B$2:$B$500))

Here is a breakdown of what those inputs mean:

First, the IF statement finds all rows where the Employee = “Edgar”; it then stores all of the corresponding “Task Time” values in an array. The MEDIAN function then pulls the median out of that array of sales cycles for “Edgar”.

3) There’s just one more key ingredient needed to make this formula work. You need to press CTRL + SHIFT + ENTER while you are still active in the cell. This is how you tell Excel that you want it to create an array. Once you do this, something magical happens… you will see curly brackets {} appear around your formula. (Unfortunately, if you type in the curly brackets yourself, Excel won’t understand what they mean.)

If you want to match 2 different values, try this:

=MEDIAN(IF(((LEFT(‘Sheet1’!$J$2:$J$5000,4)=”1234″)+(LEFT(‘Sheet1′!$J$2:$J$5000,4))=”5678″),’Sheet1’!$A$2:$A$5000,””))