Forum Discussion
Calculation method for Quartile
Unfortunately, MSFT has been "sanitizing" the function help pages, removing a lot of useful stuff.
I found the following explanation in the PERCENTILE.INC online help page (click here😞
``If k is not a multiple of 1/(n - 1), PERCENTILE.INC interpolates to determine the value at the k-th percentile.``
For QUARTILE (QUARTILE.INC), "k" would be 0, 25%, 50% 75% and 100%.
Unfortunately, MSFT help pages are unreliable in general. I would not trust that kind of information without some careful testing.
It might be helpful if you posted some examples where your calculation differs -- and show how you are calculating your results.
I do see your one example. But you do not "show your work". And some more examples might be helpful for inferring the algorithm.
(I will also try to find a set of examples that might be dispositive.)
As you noted, that does not make either method more or less correct, since statisticians do not agree on methods for interpolating percentiles -- or not.
Unfortunately, MSFT has been "sanitizing" the function help pages, removing a lot of useful stuff.
I found the following explanation in the PERCENTILE.INC online help page (click here😞
``If k is not a multiple of 1/(n - 1), PERCENTILE.INC interpolates to determine the value at the k-th percentile.``
For QUARTILE (QUARTILE.INC), "k" would be 0, 25%, 50% 75% and 100%.
Unfortunately, MSFT help pages are unreliable in general. I would not trust that kind of information without some careful testing.
It might be helpful if you posted some examples where your calculation differs -- and show how you are calculating your results.
I do see your one example. But you do not "show your work". And some more examples might be helpful for inferring the algorithm.
(I will also try to find a set of examples that might be dispositive.)
As you noted, that does not make either method more or less correct, since statisticians do not agree on methods for interpolating percentiles -- or not.
- Joe, thank you very much for this information. I did research based on this information and glad that I finally figured out the algorithm behind this:
Sample dataset with even number set: 1, 1, 2, 3, 5, 9, 17, 35, using special ruler to measure the distance: https://dsearls.org/other/CalculatingQuartiles/CalculatingQuartiles.htm
- QUARTILE.EXC: it extends the measurement of the distance as 0 to 9. On the ruler, we see point of 2.25 as the 1st quartile, point of 6.75 as the 3nd quartile, as we divided distance 9 by 4 = 2.25. Thus, we transfer the measurement to our dataset. the 1st quartile falls 1/4 of the way between 1 and 2, which is 1.25. The 3nd quartile falls 3/4 of the way between 9 and 17, which is 15.
- QUARTILE.INC: it measures the distance from 1 to 7. On the ruler, we divide 7 by 4 = 1.75 for each quarter of quartile. So, the 1st quartile is 3/4 of the way between 1 and 2, which is 1.75. The 3nd quartile falls 1/4 of the way between 9 and 17, which is 11.
- Manual calculation: the 1st quartile is between 1 and 2 = 1.5, the 3nd quartile is between 9 and 17, which is 13. This result is between QUARTILE.EXC AND QUARTILE.INC. It may be more useful for the general public or business analysis.
