[Solved] Help calculating median Age group for data set

[Francilla]

Am new how can i get help in calculating the median age group for this group data

 

[Lutfor Rahman Shimanto]

Am new how can i get help in calculating the median age group for this group data

Hello Francilla

Thanks for reaching out and posting your query. You wanted to calculate the median age group for a grouped dataset. I am delighted to inform you that I have solved your mentioned problem. I will introduce you to a mini calculator regarding your issue with error handling.

When solving your issue, I went through an article and found it very helpful. I recommend you go through the Article by clicking the following link.

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

Steps:

1. Cumulative Frequency​

=SUM($C$5:C5)

​

​

2. Total Frequency​

=SUM($C$5:$C$22)

​

​

3. Median Position​

=$C$24/2

​

​

4. Median Age Group​

=INDEX($B$5:$B$22, MATCH(TRUE, $D$5:$D$22>=$F$5,0))

​

​

5. Median Age​

=LEFT($F$8,2)+($F$5-INDEX($D$5:$D$22, MATCH(VLOOKUP($F$8, $B$5:$D$22, 3, TRUE),$D$5:$D$22, 0) - 1))*(RIGHT($F$8,2)-LEFT($F$8,2))/$C$24

​

​

You can use the following formula to handle Errors when calculating the Median Age.

=IFERROR(LEFT($F$8,2)+($F$5-INDEX($D$5:$D$22, MATCH(VLOOKUP($F$8, $B$5:$D$22, 3, TRUE), $D$5:$D$22, 0) - 1))*(RIGHT($F$8,2)-LEFT($F$8,2))/$C$24,LEFT($F$8,2)+($F$5-INDEX($D$5:$D$22, MATCH(VLOOKUP($F$8, $B$5:$D$22, 3, TRUE), $D$5:$D$22, 0) - 1))*4/$C$24)

I have attached the solution Workbook to help you understand better. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto

 

[Francilla]

Hello Francilla

Thanks for reaching out and posting your query. You wanted to calculate the median age group for a grouped dataset. I am delighted to inform you that I have solved your mentioned problem. I will introduce you to a mini calculator regarding your issue with error handling.

When solving your issue, I went through an article and found it very helpful. I recommend you go through the Article by clicking the following link.

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

Steps:

1. Cumulative Frequency​

​

​

View attachment 537​

2. Total Frequency​

​

​

View attachment 538​

3. Median Position​

​

​

View attachment 539​

4. Median Age Group​

​

​

View attachment 540​

5. Median Age​

​

​

View attachment 541​

You can use the following formula to handle Errors when calculating the Median Age.


I have attached the solution Workbook to help you understand better. Don't hesitate to contact us again if you have any more questions.

Regards
Lutfor Rahman Shimanto

Grateful for your assistance

 

[Lutfor Rahman Shimanto]

Grateful for your assistance

Greetings from ExcelDemy Forum. You are most welcome! Francilla.

 

[Francilla]

Good day i would like some help in solving these calculations i am not getting the final part of the formula correct

Grateful for your assistance

 

[Francilla]

Grateful for your assistance am having difficulties reaching the final stage of the calculations

 

[Lutfor Rahman Shimanto]

Good day i would like some help in solving these calculations i am not getting the final part of the formula correct

Dear Francilla

Sorry for the late reply. Currently, I am going through the issue you have mentioned. I will share the solution in this thread as soon as I solve the problem. Good luck!

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

Grateful for your assistance am having difficulties reaching the final stage of the calculations

Dear Francilla

Thanks again for staying with us. I have investigated the workbook you provided. In sheet1, I have calculated the median for 2018, 2019 and 2020. Through the following sheets, I highlighted with green color where you were doing correct or wrong calculations.

I have implemented the following statistical formula in Excel.

Median Formula (For grouped data):

Median = l + (n / 2 - c) / f × h

• l = Lower limit of the median class
• n = Total number of frequency
• c = Cumulative frequency of the preceding class
• f = Frequency of median class
• h = Class interval (Upper-class limit - Lower-class limit)

Excel Formula:

=LEFT($C$28,2)+($D$24-INDEX($E$5:$E$21, MATCH(VLOOKUP($C$28, $C$5:$E$21, 3, TRUE), $E$5:$E$21, 0) - 1))*(RIGHT($C$28,2)-LEFT($C$28,2))/$D$22

Solutions:

​

If you want to learn how the mentioned Excel Formula works, let me know. I am attaching the solution workbook to help you understand better.

Regards
Lutfor Rahman Shimanto

 

[Francilla]

I am so thankful for your help, yes i would like to learn the process

 

[Lutfor Rahman Shimanto]

I am so thankful for your help, yes i would like to learn the process

Dear Francilla

I am incredibly sorry that the formula I previously provided contains a mistake. I noticed that mistake when I started to break down the formula for you. I mistakenly used total frequency as f.

However, I am all aware now. I have developed an Excel Formula derived from the statistical median formula. As promised, I will explain the formula breakdown to you.

Corrected Formula:

=LEFT($G$8,2)+($G$5-INDEX($D$5:$D$22, MATCH(VLOOKUP($G$8, $B$5:$D$22, 3, TRUE), $D$5:$D$22, 0) - 1))*(RIGHT($G$8,2)-LEFT($G$8,2))/VLOOKUP(G8,B5:D21,2,FALSE)

OUTPUT:

​

Explanation:

​

I have attached the solution workbook, and thank you again.

Regards
Lutfor Rahman Shimanto