Third Quartile (75th Percentile

[Atanas]

I know the definition of 3rd quartile and I know the formula in excel but
could not figure out how excel calculates it so I can explain it at my trial.
My population is relatively small 1.22x, 1.12x, 3.11x and 0.85x. the median
is 1.17x the 1st quartile is 1.05x and the 3rd quartile is 1.69x. I tried to
figure out how excel calculates these quartiles when the population is not
big enough. I really appreciate your help and thank you in advance.

 

[Mike Middleton]

Atanas -

Excel assigns cumulative relative frequency of zero to the minimum value of
the data set and cumulative relative frequency of one to the maximum value.
Intermediate values of the data set are assigned cumulative relative
frequency in steps of 1/(n-1), where n is the number of values in the data
set.

To determine intermediate percentiles, Excel uses linear interpolation
between two cumulative relative frequencies.

For n=4, the four values are assigned cumulative relative frequencies 0.0,
0.33333..., 0.66666..., and 1.0.

The 3rd quartile has cumulative relative frequency 0.75. The interpolation
between your data values 1.22 and 3.11 is
1.22+(3.11-1.22)*(0.75-0.66667)/(1.0-0.66667) = 1.22+1.89*0.08333/0.33333 =
1.22+1.89*0.25 = 1.22+0.47 = 1.69.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Stan Brown]

Mon, 21 Jan 2008 13:23:13 -0800 from Mike Middleton

Excel assigns cumulative relative frequency of zero to the minimum value of
the data set and cumulative relative frequency of one to the maximum value.

Is that accurate? I should have thought cumulative relative
frequency of the minimum number would be 1/N for a data set of N
items.

 

[Mike Middleton]

Stan Brown -

I think my description of Excel's method is "accurate."

But there are several ways to determine percentiles. I was only trying to
describe Excel's method. Browse to www.google.com, and search for
"percentile," or take a look at Wikipedia's entry.

Personally, I prefer starting the cumulative relative frequencies at 1/(2*n)
for the minimum value and using steps of 1/n up to 1-(1/(2*n)) for the
maximum. (Justification: The minimum value represents the lower 1/n
cumulative relative frequencies, so I associate the minimum value with the
median cumulative relative frequency, i.e., 1/(2*n).)

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Sloth]

This seems an odd way to do this, but apparantly there is no universally
accepted method for quartile so you can't say Excell is incorrect in doing it
this way. I copied and pasted the following from
http://www.staff.city.ac.uk/r.j.gerrard/excelfaq/faq.html

If the data are assumed to be in ascending order,
- the lower quartile (Q1) is the ¼(n+3)th observation
- the second quartile (median) is the ½(n+1)th observation
- the upper quartile (Q3) is the ¼(3n+1)th observation
When ¼(n+3), ½(n+1) and ¼(3n+1) are not integers, linear interpolation is
used.