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 for 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.


Download Practice Workbook

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


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.


2 Ways to Calculate Median Age of Population in Excel

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

How to Calculate Median Age of Population in Excel


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-
=INDEX(B5:B20,MATCH(SUM($C$5:$C$20)/2,SUMIF($B$5:$B$20,"<= "&$B$5:$B$20,$C$5:$C$20)))
  • 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.

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

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

Read More: How to Calculate Population Proportion in Excel (with Easy Steps)


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.

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

Steps:

  • First, we’ll have to calculate the cumulative frequency. For that, add a column and insert the following formula and use the Fill Handle tool-
=SUM($C$5:C5)

Find Cumulative Frequency to Calculate Median Age of Population from an Aggregated Frequency Table

  • After that, we’ll calculate the total population, for that, type the following formula in Cell F5
=SUM(C5:C20)
  • 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-
=F5/2

Our middle point is 41578. From the table, we are seeing 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-
=45.5+10*((83156/2-40855)/8845)
  • 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

Read More: Population Projection Formula in Excel (3 Applications)


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.


Related Articles

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo