QUARTILE.EXC
"If the requested quartile falls between two of the values in the supplied array, Excel interpolates between these values to calculate the quartile value."
https://www.excelfunctions.net/excel-quartile-exc-function.html
Best
D
I understand that Anonymous, I just couldn't figure out how but now I think I understand how it is doing the interpolation but I can't decide if that is the correct way it should be doing it because it doesn't seem to match up with the way anybody else does it that I can find. And it gets really bizarre when you consider QUARTILE and QUARTILE.INC for this problem.
So if I understand what it is doing (Excel), it uses the standard quartile 1 formula, 1/4 * (n + 1) to find the "rank" for the first quartile. This produces 2.25 meaning that it should interpolate between 2nd and 3rd numbers which are 2 and 4. If you shortcut the math, .25 of 2 is .5 so 2 + .5 is 2.5. Same with quartile 3, 3/4 * (n + 1) would be 6.75 so interpolate between the 6th and 7th numbers, which are 9 and 10, shortcut .75 * 1 = .75 so 9 + .75 = 9.75. All's fair here until you realize that QUARTILE and QUARTILE.INC in Excel return 3.5 for quartile 1 and 9.25 for quartile 3. This is really where I am struggling because I don't see how you get to those numbers unless for some reason you are interpolating from the higher number for .INC and interpolating like normal people interpolate for the .EXC versions. In other words, the interpolation is .5 for quartile 1 so you say 4 - .5 = 3.5 and 10 - .75 = 9.25. But the question has to be why? Why are you interpolating in two completely different ways? That's the real difference between .INC and .EXC if that is the case. And I still don't think that either method is what is done in best practices at least as far as I can tell from researching this on the Internet.
So, I understand interpolation, I had to do enough of it in thermodynamics and fluid dynamics to last me a lifetime. What I don't understand is how Excel is doing its interpolation because it doesn't make any sense and it seems to do it one way sometimes and another way other times.
OK, one versus:
- https://www.easycalculation.com/statistics/inter-quartile-range.php
- http://web.mnstate.edu/peil/MDEV102/U4/S36/S363.html
- https://www.thoughtco.com/what-are-first-and-third-quartiles-3126235
- https://www.calculatorsoup.com/calculators/statistics/quartile-calculator.php
- https://miniwebtool.com/quartile-calculator/
- https://www.mathportal.org/calculators/statistics-calculator/descriptive-statistics-calculator.php
- https://www.mathsisfun.com/data/quartiles.html
I could go on, it's like the first three pages of search results. But, the important question is how in the world is the difference in interpolation techniques not documented in the explanation of these functions PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC and QUARTILE.EXC but instead focuses on what values of "k" you can pass into them? I mean, it makes no sense. How does INC translate into N-1 and EXC translate into N+1 and why not just use N as John Peltier himself states?
".INC is the same as Excel’s “inclusive” (N-1) legacy functions, and .EXC is the same as everyone else’s “exclusive” (N+1) functions. I don’t really know why anyone would pick either (N-1) or (N+1) over the other, or why they’d pick either of these over the N-basis calculations."
- It's a question for M$. After all, you can contact them and ask. But does it really matter? Since there are slightly different definitions and they are all asymptotically the same, I wouldn't make a fuss about it. That there is no documentation is a different matter, though.
Best
D