Median for frequency distribution

[Guest]

How can I calculate descriptive statistics for the following frequency
distribution?
Age Group Frequency
15-20 59
20-25 197
25-30 263
30-35 235
35-40 142
40-45 60
45-50 21

 

[Lori]

With data in the first two columns and the lower end of the range in
the first column:

A B
15 59
20 197
25 263
30 235
35 142
40 60
45 21

the estimated descriptive stats:

Avg 29.90
StDev 6.95
Skewness 0.32
Kurtosis 2.64
Median 24.42
Quartile1 19.70
Quartile2 29.55

To find the first four quantities it's standard to use the midpoint of
the ranges for the calculations. So for the avg add 2.5 to each value
in the first column multiplied by the relative frequency. For stdev
subtract the avg from each value squared times the frequency and for
skew/kurt normalise the data by the StDev.

For the quartiles, calculate the cumulative frequencies and
interpolate the value at the 25%,50% and 75% points

Formulae used:

Avg:
=SUMPRODUCT(A1:A7+2.5,B1:B7)/SUM(B1:B7)

StDev:
=(SUMPRODUCT((A1:A7+2.5-E1)^2,B1:B7)/SUM(B1:B7))^0.5

Skew (N=3) / Kurtosis (N=4):
=SUMPRODUCT(((A1:A7+2.5-Avg)/StdDv)^N,B1:B7)/SUM(B1:B7)

Median (p=0.5) / Quartile1 (p=0.25) / Quartile2 (p=0.75):
=PERCENTILE(A1:A7,PERCENTRANK(PROB(A1:A7,B1:B7/SUM(B1:B7),,A1:A7),p,
20))

The last needs to be array entered (ctrl+shift+enter to execute)

 

[David Biddulph]

If you want to find the median (and in future, please remember to ask the
detailed question in the message body, not the subject line), then you need
to calculate the cumulative distribution, & see where that reaches 50% of
the population.

 

[Guest]

Thanks Lori. Except that the formula for PROB have lower limits in place of
upper limits which is reducing median and other percentiles by 5 years. I put
the upper limits in the first column instead of lower limits and it is
perfect.

20 59
25 197
30 263
35 235
40 142
45 60
50 21

 

[Lori]

You're right, calculations were done quickly and should have spotted
that there was a large difference between median and mean. The moments
should be correct.

Glad it worked for you anyway.