Apache OpenOffice (AOO) Bugzilla – Issue 69712
Implement true QUARTILE function
Last modified: 2017-05-20 11:13:15 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: http://www.daheiser.info/excel/notes/notew.pdf http://www.cs.uiowa.edu/~jcryer/JSMTalk2001.pdf 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.
Created attachment 39277 [details] errors in QUARTILE()
Hi, 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 ? Frank
Hi Frank, http://www.daheiser.info/excel/notes/noteh.pdf 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: http://mathworld.wolfram.com/Quartile.html If you'd like to close this issue as WONTFIX (with short explanation), I can file another feature request issue. WBR, Mike
Hi Eike, please have a look at this Issue and comment on it and set target and Issue type according to your decission. Frank
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.
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.
Reset assigne to the default "issues@openoffice.apache.org".