Calculation method for Quartile

[AmyG123]

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

[#2 · Accepted solution]

@AmyG123 

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.

[#3]

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.