Results 1 to 3 of 3
Thread: How Does QUARTILE Work

20010504, 16:48 #1
 Join Date
 Jan 2001
 Location
 Atlanta
 Posts
 569
 Thanks
 7
 Thanked 1 Time in 1 Post
How Does QUARTILE Work
I am interested in knowing how the QUARTILE and PERCENTILE functions work. As best as I can figure, QUARTILE computes the position of the quartile using a formula similar to:
((k*n)+1)/4
where k is the quartile requested (13). However, the results I get manually calculating using this formula are only approximately what Excel gets. The help system does not give its exact approach. Does anyone know?
I am also interested in the PERCENTILE function. Here, the help system is slightly more helpful. It says Excel interpolates to determine the value at the kth percentile but a simple linear interpolation does not match the values that Excel is returning. Does anyone know more?Ronny Richardson

20010504, 18:57 #2
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: How Does QUARTILE Work
Ronny,
It is not as simple as that. You have to calculate the ranks of the data. As an example, suppose you have a list of values in the range B1:B117. In column C you calculate the rank of each value in the range, like this
=RANK(B1;$B$1:$B$117;1)
This gives you the rank of the value in B1 in the list of values B1:B117. You do this for all values in the range B1:B117. Suppose now that you want to calculate the 15th Percentile: =PERCENTILE(B1:B117;0.15)
Well, calculate =(COUNT(B1:B117)1)*0.15 = 17.4 (this is (n1) * Pct), where Pct is the percentile. In this example this is 17.4. This means that in the list you have to look for the element with rank 18 (which is 17.4 rounded up) and the element with rank 19. If you ordered the data ascending then the element with rank 18 will be B18 and then the 15th percentile is just equal to B18 + (B19  B18) * 0.4. The value 0.4 comes from the 17.4  the integer just below 17.4.
I admit it looks a little bit complicated, but it works. The main message is that you have to work with ranks. Quartiles work similar. Actually, =PERCENTILE(B1:B117;0.25) should be equal to =QUARTILE(B1:B117;1).

20010510, 15:55 #3
 Join Date
 Jan 2001
 Location
 Atlanta
 Posts
 569
 Thanks
 7
 Thanked 1 Time in 1 Post
Re: How Does QUARTILE Work
Thanks,
I worked through a couple of large examples and this procedure always returned the same results as quartile/percentile.
RonnyRonny Richardson