Martin, who works in the HR Department of his company, wrote to me recently with a query. It seems he had a list of ages in column A of his worksheet. The ages were from 1 to 100. In column B he had the number of folks of each age.
His query was – can you give me a formula that will result in the median age for this list of people?
Your first impulse may be to figure out the average age, but there is a difference between the average age and the median age. If you were looking for the average age, you could calculate that fairly easily by multiplying the age by the number of people of that age. In column C, you could key in a formula such as =A1*B1 and then copy it down the column, add up the values in columns B and C and divide the sum in column C by the sum in column B, which would give you the average age for the list.
The median age though, is the age where half the folks fall below that age and half above. The median age can best be calculated using an array formula such as the one below:
=MATCH (SUM ($B$1:$B$100) /2,SUMIF($A$1:$A$100,
“<= “&$A$1:$A$100,$B$1:$B$100))
This is a single formula, entered by clicking CTRL + Shift + Enter. The SUMIF function in the formula is used to generate an array of the cumulative number of people who are less than or equal to each age. The SUM portion of the formula gives the midpoint of the total frequency of ages. the MATCH function is then used to look up the midpoint value int he array of cumulative frequencies. This will yield an index number in the initial array and since the array is made up of the ages 1 through 100, the index number match is equal to the median age.