How to Calculate Median Age of Population in Excel (2 Ways)

What Is the Median?

The median is the middle value of a data array that is arranged in an ascending or descending order. For example, the median of 3,4,5,6,7 is 5.

How to Calculate the Median Age of Population in Excel: 2 Simple Ways

We’ll use a simple dataset that contains a simple age distribution.

Method 1 – Combining INDEX, MATCH, SUM, and SUMIF Functions to Calculate the Median Age of the Population

Steps:

• In Cell E5, insert the following formula:

• Hit Enter. For earlier versions of Excel, you may need to press Ctrl + Shift + Enter as it is an array formula.

Formula Breakdown:

• SUMIF($B$5:$B$20,”<= “&$B$5:$B$20,$C$5:$C$20)

The SUMIF function will create an array of the cumulative number of people for the ages. So, it will return as-

{3473;10196;16631;25554;30187;37478;40855;49700;53556;55854;58189;61145;70080;77862;80311;83156}

• SUM($C$5:$C$20)/2

Then, the SUM function of the formula will return the midpoint of the total count of the population. The midpoint will return as

41578

• MATCH(SUM($C$5:$C$20)/2,SUMIF($B$5:$B$20,”<= “&$B$5:$B$20,$C$5:$C$20))

The MATCH function will look up the midpoint value from the array of cumulative counting. It will return a serial/index number according to the array. It will return-

7

• INDEX(B5:B20,MATCH(SUM($C$5:$C$20)/2,SUMIF($B$5:$B$20,”<= “&$B$5:$B$20,$C$5:$C$20)))

The INDEX function will return the age number from the age list according to the serial/index number. Finally, the formula will return as

24

Method 2 – Using a Basic Statistical Formula to Calculate the Median Age of a Population from an Aggregated Frequency Table

The basic statistical formula for the median is:

• Median = L+w*(n/2−c)/f

Where,

• L = Lower limit of the class containing the median.
• w = Width of that class.
• n = Total population.
• c = Cumulative frequency of a population.

We modified the dataset.

Steps:

• Add a column for the frequency, insert the following formula in the first cell, then use AutoFill to fill the column.

• Use the following formula in Cell F5:

• Hit Enter.

Now we’ll find the class of median by using the middle point of the population. To get the middle point, use n/2 for an even number, and use (n+1)/2 for an odd number.

• Use the following formula in Cell F7 and hit the Enter button to get the middle point:

Our middle point is 41578. From the table, we see that it lies in class 46-55. So, it is our median class. That means the median age lies between 46 to 55.

• Insert all the relevant data in a formula in Excel following the basic statistical formula:

• Press the Enter button to finish.

Note: Here, we calculated L by making the average of the last value of the median class and the end value of the previous class, L = (45+46)/2 = 45.5

Download the Practice Workbook

<< Go Back to How to Calculate Median in Excel | Excel for Statistics | Learn Excel