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

While counting population, the median is one of the major factors to calculate. Different kinds of medians have to calculate different kinds of data about a population. In this article, we’ll deal with the age of the population, so we’ll show how to calculate the median age of the population in Excel following 2 fast methods.

What Is Median?

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

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

First of all, get introduced to our dataset that contains the population number for every age of 18 to 33.

1. Combining INDEX, MATCH, SUM, and SUMIF Functions to Calculate Median Age of Population

In our first method, we’ll combine the INDEX, MATCH, SUM, and SUMIF functions to calculate the median age of the population in Excel.

Steps:

• In Cell E5, insert the following formula-

• Then just hit the Enter button to get the median age from the list.

Note: 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))

Later, 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)))

Finally, 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

2. Using Basic Statistical Formula to Calculate Median Age of Population from an Aggregated Frequency Table

If the ages remain with a different class of ranges in a table then we will have to use the basic statistical formula to calculate the median age of the population from the table in Excel.

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.

For this method, we modified the dataset like the image below.

Steps:

• First, we’ll have to calculate the cumulative frequency. For that, add a column and insert the following formula. Then, use the Fill Handle tool

• After that, we’ll calculate the total population, for that, type the following formula in Cell F5

• Next, just press the Enter button.

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.

• Write 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.

All the data are ready, now we’ll calculate the median age.

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

• Finally, 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 Practice Workbook

You can download the free Excel workbook from here and practice independently.

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to calculate the median age of the population in Excel. Feel free to ask any question in the comment section and please give me feedback.