Finding Median Age

  • Thread starter Thread starter BStacy
  • Start date Start date
B

BStacy

Is there a function that will allow me to find a median age?

The data I have is in the following format:

Age Range Population
0-14 45
15-17 23
18-24 31
35-54 52
55-64 28
65+ 30
 
This is tricky.

First, you'd have to have an odd number of age categories. If you have an
even number, then MEDIAN take the average of the two Medians to come up with
an artificial median not present in your list.

Second, if you had a Median value which shows up twice (let's say the
population count which creates a median is 30 but you have two age categories
which have a population of 30), then the first age bracket is what would turn
up.

If you can get past both of those issues (have an odd number of age brackets
every time and have a unique population count in each age bracket), then this
will give you the age bracket which falls within your Median:
=INDEX(A2:A8,MATCH(MEDIAN(B2:B8),B2:B8,0))
 
You can't calculate the median, the best you can do is to create an estimate.

For example in A1 thru A45, enter:
=RANDBETWEEN(0,14)

In A46 thru A68, enter:
=RANDBETWEEN(15,17)

and continue for each of the segments. Then use =MEDIAN(A:A)

each time you re-calculate the spreadsheet, you will get a new estimate of
the median. Save a pile of these estimates and calculate the MEAN of the
pile.

This should represent a good estimate of the median
 
This finds the median age bracket but not the median age. How will I find
the median age?
 
Let us know how you will calculate MEDIAN on paper with the data you
have...We can try to convert that to Excel requirements...
 
You can't, as Gary's Student points out, because you have not provided a data
set which includes every person listed with their age. You've chosen to
categorize your data into age brackers, so you'll get the median category
(age bracket), not the median age.

You either need to abandon the age bracket idea and input every person with
their age or take Gary's Student's suggestion of giving each person in your
population a row but faking their age based on their category....fake their
ages a whole bunch of statistically relevant times and record the median,
then take the MEAN of those results, and you'll be close but not 100%
accurate.
 
I came across this thread trying to do my own median age calculation, but the formula kept returning a median figure that was much too high. Other formula I found online also didn't seem to work.

So I went back to the old fashioned way which was frankly less trouble, and it doesn't matter whether you have an odd or even number of categories.

I calculated cumulative totals, so for your data:
.................Cumulative total
0-14.... 45.... 45
15-17 ...23... 68
18-24 ...31 ...99
35-54 ...52 ...151
55-64 ...28 ...179
65+ ....30.... 209

I divided the total number of people by 2:

209/2= 104.5

The 104th person is in the 35-54 category.

As others have suggested, it is difficult to be more accurate than this with a median. although we can guess the 104th person is closer to 35 than 54, its hard to be sure how evenly the data are distributed within this age band. If you want more reliable detail, you really need disaggregated data.

Of course, this method won't help you if you want to be able to add data at a later date and have the result update automatically, but if you're looking for a quick answer it may be simpler and more fool-proof than using excel formulae.
 
Back
Top