Last week, during an Excel training course that I was delivering, I demonstrated how to display the average in a pivot table. I was then asked “how can you display the median?”.

This isn’t the place for a detailed discussion about average v median (I’m not a mathematician and you’re probably not interested) but in a nutshell, the average is the sum of a set of numbers divided by the number of numbers whereas the median is the middle point of a set of numbers, in which the numbers are placed in ascending order and half the numbers are above the median and half are below. So back to Excel…

It’s not as simple as clicking the drop down arrow in the Values section, selecting Value Field Settings and selecting “Median” as median does not exist as an option…

In fact you can’t actually display the median in a pivot table. You have to create a formula manually and copy it down. The screenshot below contains fictitious response times in minutes for police officers responding to calls over a 7-day period. As you can see from the values in F1 and F2, the overall average and the overall median are different.

F1 contains the formula =AVERAGE(C2:C50)
F2 contains the formula =MEDIAN(C2:C50)

To create the median values in column G, I typed the word “Median” in G4 and formatted G4 with the same font, colour and cell background as F4.

I then typed the following formula into G5 (which I then copied down to G6:G11)…

{=MEDIAN(IF($B$2:$B$50=E5,$C$2:$C$50,””))}

The first thing that you notice is that there is a “curly bracket/brace” at the start and end of the formula. This indicates that it is an “array formula”. An array formula is a formula that works with an array, or series, of data values rather than a single data value.

Note – you must not type the curly braces yourself. To enter a formula as an array formula, type it in the usual way but instead of pressing Enter when done, press CTRL+SHIFT+ENTER. Excel will add the curly braces.

So what does the formula do? It calculates the median of the numbers in C2:C50 but only where the word in B2:B50 is equal to the value in E5 (Monday). The reason for the dollar signs is so that when the formula is copied to G6:G11 it retains the references to B2:B50 and C2:C50.

Download the sample workbook