Issue 69712 - Implement true QUARTILE function
Summary: Implement true QUARTILE function
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.3
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2006-09-21 06:17 UTC by pmike
Modified: 2017-05-20 11:13 UTC (History)
1 user (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

errors in QUARTILE() (10.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2006-09-21 06:18 UTC, pmike
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description pmike 2006-09-21 06:17:34 UTC
It seems that QUARTILE() function always calculate its result as simple average
of two adjacent values. This is not correct.
The correct formulaes are (where N is number of elements):
(N+1)/4 for QUARTILE(,1);
(N+1)/2 for QUARTILE(,2);
3(N+1)/4 for QUARTILE(,3).
generally: X(N+1)/4 for QUARTILE(,X), where X={1,2,3}

Note: Excel also works incorrectly, but I suppose this is the case we could
break 'compatibility' with errorneous behavior of Excel.
Statistical applications like SPSS, Minitab and PHStat for Excel works correctly.
Some info about Excel faults:
or simply google for "Excel wrong QUARTILE"

The sample file shows errorneous calculations. Red values are wrong, green
values are OK, and blue values are simple averages.
Comment 1 pmike 2006-09-21 06:18:07 UTC
Created attachment 39277 [details]
errors in QUARTILE()
Comment 2 frank 2006-09-21 09:11:52 UTC

have you seen the Appendix H pdf ? as far as I understand it, the Method Excel
uses is not entirely wrong as there is no standard defined. Aswe calculate as
Excel, I think this Issue has to be closed. What do you think ?

Comment 3 pmike 2006-09-21 09:53:37 UTC
Hi Frank,
Really, there is no standard for quartiles calculation, therefore no 'strong'
reasons to claim incorrectness of Excel calculations. However, QUARTILE() is a
statistical function and sticking with one (and probably not most popular due to
differences with statistic-oriented software) is not good idea.

So, my proporsal would be: add third optional parameter to QUARTILE() function,
from 1 to 14, to choose method of quartile calculation. Default will be
Excel-compatible method. This gives Calc good advantage in compare to Excel, and
makes Calc more useful for statistical analysis.
Here is some info about different methods of quartiles calculation in use:

If you'd like to close this issue as WONTFIX (with short explanation),
I can file another feature request issue.

WBR, Mike
Comment 4 frank 2006-09-21 10:00:28 UTC
Hi Eike,

please have a look at this Issue and comment on it and set target and Issue type
according to your decission.

Comment 5 pmike 2006-09-22 06:06:58 UTC
David Levin and Andrew F. Siegel, the authors of popular statistical textbooks
recommend X(N+1)/4 method for quartiles calculation.

1. David. M. Levine, David Stephan, Tymothy C. Krehbiel, Mark Berenson - Statistics 
   for Managers using Microsoft Excel, 4-th edition, Prentice Hall, 2005.
2  SIEGEL,ANDREW F. Statistics and Data Analysis: An Introduction. Second Edition.,
   John Wiley & Sons 1996, 2nd edition, Hardcover. ISBN:0471574244

The Gumbell method is quite rare and only have limited application for financial
market analysis.
Comment 6 ooo 2006-09-25 14:19:08 UTC
Calculating as Excel does is on purpose. Adding an additional parameter is not
an option due to interoperability issues. A second "true" statistical quartile
function could be implemented.
Comment 7 Marcus 2017-05-20 11:13:15 UTC
Reset assigne to the default "".