Forum Discussion
Solved
Calculation method for Quartile
It is about different results between Excel Quartile function and manual calculation. Though this question has been asked previously by someone else. I didn't see a solution.
For even numbered dataset, the lower quartile and upper quartile are different from statistic methods. https://support.microsoft.com/en-us/office/quartile-function-93cf8f62-60cd-4fdb-8a92-8451041e1a2a The example shows the 1st quartile is 3.5, while I calculated the 1st quartile is 3. I understand that there are different formulas to calculate quartiles. Does anyone find out the formula used by Excel or solution? Thank you.
Amy
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.
