Finding Median Age

[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

 

[KC Rippstein hotmail com>]

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

 

[Gary''s Student]

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

 

[BStacy]

This finds the median age bracket but not the median age. How will I find
the median age?

 

[Sheeloo]

Let us know how you will calculate MEDIAN on paper with the data you
have...We can try to convert that to Excel requirements...

 

[KC Rippstein hotmail com>]

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.