Issue 40833 - Bad calculation of standard deviation, variance, ...
Bad calculation of standard deviation, variance, ...
 Status: CLOSED DUPLICATE of issue 22811 None Calc Application code (show other issues) OOo 1.1.4 PC Windows XP P3 Trivial (vote) --- kla issues@sc

 Reported: 2005-01-17 18:55 UTC by nicolasduvernois 2005-01-19 09:30 UTC (History) 1 user (show) issues ENHANCEMENT --- ---

Attachments
file describing problem and suggested solution (34.11 KB, application/vnd.sun.xml.calc)
2005-01-17 18:56 UTC, nicolasduvernois
no flags Details

 Note You need to log in before you can comment on or make changes to this issue.
 nicolasduvernois 2005-01-17 18:55:26 UTC Calculation of standard deviation is done in a way which is not optimal as far as calculation precision is concerned. See attached for details : - the way calculation is probably done (for variance) is : (n * Sx2 - (Sx)^2) / (n * n(-1)) - I suggest the following formula : S(x-x_avg)^2/(n-1) where : n = number of data Sx2 = sum of all x^2 Sx = sum of all x x_avg = average of x The first formula has the following drawback : if the variation of x is small compared to x_avg, then loss of precision may occur. In this case, the result of (n * Sx2 - (Sx)^2) my be either zero or negative. With the second formula, since the denominator is a sum of positive data (sum of the square of all differences between x and x_avg), the result can only be positive (and non-zero as soon as there are 2 different x values). The same problem is valid for : - var - var.p - var.pa - stddev (exact spelling to be checked, I'm using the french version !) - stddev.p - linear regression - ... nicolasduvernois 2005-01-17 18:56:08 UTC Created file describing problem and suggested solution frank 2005-01-19 09:30:23 UTC Hi, this is a double to Issue 22811 . BTW formulas and functions are for Spreadsheet and not Chart. Chart is the visualisation part for Spreadsheet data. Frank *** This issue has been marked as a duplicate of 22811 *** frank 2005-01-19 09:30:41 UTC closed double