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.

**Table of Contents**hide

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

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

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

**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)`

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