Issue 101316 - STDEV is inaccurate
Summary: STDEV is inaccurate
Status: CLOSED FIXED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 3.0.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
URL:
Keywords: oooqa
Depends on:
Blocks: 18704
  Show dependency tree
 
Reported: 2009-04-24 07:35 UTC by zoominee
Modified: 2017-05-20 11:42 UTC (History)
2 users (show)

See Also:
Issue Type: PATCH
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Illustrate wrong STDEV (8.38 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-04-24 07:36 UTC, zoominee 		no flags Details
exchange minus with approxSub; only 1 line (504 bytes, text/plain)
2009-04-26 22:26 UTC, Regina Henschel 		no flags Details
some examples of the error (38.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-04-26 22:27 UTC, Regina Henschel 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description zoominee 2009-04-24 07:35:26 UTC 
STDEV should return 0 when all numbers are equal.
Enter 1e+30 in A1, A2, A3, and =STDEV(A1:A3) in A4. The result should be 0, but 
it is 172367517075721. (Wrong)
Suggested fixes: Centre data before calculating STDEV, use a better algorithm.
Some Excel versions suffer from the same problem: see http://www.daheiser.info/
excel/frontpage.html link to Note O.
Comment 1 zoominee 2009-04-24 07:36:20 UTC 
Created attachment 61770 [details]
Illustrate wrong STDEV
Comment 2 Regina Henschel 2009-04-25 11:18:32 UTC 
I see the error too in a DEV300m44. There is no obvious reason. For 1E+29 the
result is correct. It needs a deeper look at. I add dependency to 18704.
Comment 3 Regina Henschel 2009-04-26 22:20:38 UTC 
The error comes from cancellation with total loss of significance in value-mean
in some cases. It seems to be the same kind of error as the known "3*1/3 != 1"
problem.

I see the error only, when using identical values in the data series, which
seems to be a very constructed example. But the results are totally wrong in
those cases. I'll attach test cases, where you see the error examples with
colored background. You can notice, that the values are correct, if the
denominator is 4, which can be represented exactly in binary code.
I looked at Gnumeric and Excel too, neither of them correct this kind of errors. 

You can correct the error, if you use approxSub instead of a simple minus. I
have tried to find out, whether this change will decrease accuracy in other
cases. I don't find any examples, which would have a decreased accuracy, but
perhaps you have got some critical test case?
Comment 4 Regina Henschel 2009-04-26 22:26:20 UTC 
Created attachment 61825 [details]
exchange minus with approxSub; only 1 line
Comment 5 Regina Henschel 2009-04-26 22:27:18 UTC 
Created attachment 61826 [details]
some examples of the error
Comment 6 ooo 2009-04-28 08:30:38 UTC 
Reproduced on Linux/x86. Grabbing issue, changing type to PATCH.
Comment 7 ooo 2009-04-30 00:56:58 UTC 
In cws odff06:

revision 271399
sc/source/core/tool/interpr1.cxx

We may want to keep an eye on accuracy with other sample values that could make
it necessary to pre-analyze the values for equality. A similarity to SUM and
such comes to mind..
Comment 8 ooo 2009-09-03 16:38:05 UTC 
Reassigning to QA for verification.
Comment 9 oc 2009-09-08 13:33:39 UTC 
verified in internal build cws_odff06