ExcelBanter - Median for frequency distribution

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Median for frequency distribution (https://www.excelbanter.com/excel-worksheet-functions/135558-median-frequency-distribution.html)

Median for frequency distribution

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

Answer: Median for frequency distribution

To calculate the median for this frequency distribution, you will need to follow these steps:

Find the total frequency by adding up all the frequencies in the table. In this case, the total frequency is 977.
Determine the midpoint of the frequency distribution for each age group. To do this, add the lower and upper limits of each age group and divide by 2. For example, the midpoint for the age group 15-20 is (15+20)/2 = 17.5.
Calculate the cumulative frequency for each age group by adding up the frequencies from the first age group to the current age group. For example, the cumulative frequency for the age group 20-25 is 59+197 = 256.
Identify the median age group. This is the age group that contains the middle value of the distribution. To find this, divide the total frequency by 2 to get 488.5. Then, find the age group that contains this value in the cumulative frequency column. In this case, it is the age group 25-30.
Calculate the median age. To do this, use the formula:

Median = L + ((N/2 - CF)/f) x w

Whe
L = lower limit of the median age group (25)
N = total frequency (977)
CF = cumulative frequency of the age group before the median age group (256)
f = frequency of the median age group (263)
w = width of the age group (5)

Plugging in the values, we get:

Median = 25 + ((488.5 - 256)/263) x 5
Median = 27.5

Therefore, the median age for this frequency distribution is 27.5.

Median for frequency distribution

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)

On 20 Mar, 09:50, Samirkc wrote:
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

Median for frequency distribution

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.
--
David Biddulph

"Samirkc" wrote in message
...
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

Median for frequency distribution

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" wrote:

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)

On 20 Mar, 09:50, Samirkc wrote:
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

Median for frequency distribution

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.

On 20 Mar, 11:11, Samirkc wrote:
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" wrote:
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)

On 20 Mar, 09:50, Samirkc wrote:
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- Hide quoted text -

- Show quoted text -