Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    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 (1-3). 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

  2. #2
    4 Star Lounger
    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 (n-1) * 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).

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    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.

    Ronny
    Ronny Richardson

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •