Excel tutorial
How to calculate medians for multiple variables in Excel
One of the shortcomings of Microsoft Excel is that you cannot easily obtain the median for several different variables. Mystery solved. At first glance, this may look complicated. But it's really not too difficult to recreate.
1. Simple median
If you want a median for all the numbers in a column (for example, numbers listed cells B2 through B500), simply type in this formula
- =median(b2:b500)
2. Multiple medians
Here's how to calculate the median for a series of categories in one spreadsheet (for example a series of home sales where the town is listed in column A and the sales price for each home is in column B). For our example, we'll assume the number of rows does not exceed 10,000.
Step 1
- Determine the unique values (towns) in Column A. If you don't know what all variables exist, use a pivot table to count the values in column A. This will produce a list of unique values (in our case, the different towns).
Step 2
- Place the list of unique values in Column D, leaving open Column C.
Step 3
- Type in this formula in cell B2, next to the first set of values. (Again this assumes the towns for all sales are listed in Column A, the sales prices are listed in Column B and the unique values from Column A are listed in Column D.)
- =MEDIAN(IF($A$2:$A$10000=D2,$B$2:$B$10000))
- IMPORTANT: Do not simply hit "enter" or "return" to leave the cell. You must tell Excel you want this to be treated as an array. To do so, hold down the control and shift keys while hitting "enter" or "return."
Step 4
- Drag to repeat the formula down through the end of your unique values.