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

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"BStacy" wrote:

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

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

--
Gary''s Student - gsnu200845


"BStacy" wrote:

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 finds the median age bracket but not the median age. How will I find
the median age?

"KC Rippstein" wrote:

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

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"BStacy" wrote:

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

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


"BStacy" wrote:

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

"KC Rippstein" wrote:

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

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"BStacy" wrote:

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

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.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"BStacy" wrote:

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

"KC Rippstein" wrote:

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

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"BStacy" wrote:

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