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

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

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

`=SUM($C$5:C5)`

- Use the following formula in
**Cell F5:**

`=SUM(C5:C20)`

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

`=F5/2`

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:

`=45.5+10*((83156/2-40855)/8845)`

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

Thank you! This was really helpful.

Hello

John,You are most welcome.

Regards

ExcelDemy