Bug 80583 - EDITING: Analysis of Variance (ANOVA): Total DEVSQ returns Err:508, while direct DEVSQ formula returns correct result
Summary: EDITING: Analysis of Variance (ANOVA): Total DEVSQ returns Err:508, while dir...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.5.2 release
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:6.3.0 target:6.2.0.1 target:6....
Keywords:
Depends on:
Blocks:
 
Reported: 2014-06-27 08:00 UTC by Kevin Suo
Modified: 2019-12-07 15:25 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Analysis of Variance test file (41.77 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-27 08:00 UTC, Kevin Suo
Details
How my test file looked in 4.3 (115.43 KB, image/png)
2014-06-27 15:17 UTC, Yousuf Philips (jay) (retired)
Details
How my test file looked in 4.2 (110.10 KB, image/png)
2014-06-27 15:17 UTC, Yousuf Philips (jay) (retired)
Details
Calc 6.2master (48.82 KB, image/png)
2018-06-10 11:46 UTC, raal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2014-06-27 08:00:08 UTC
Created attachment 101841 [details]
Analysis of Variance test file

(This bug was first reported by "林肯" in the Chinese LibreOffice discussion forum: http://www.libreofficechina.org/thread-34-1-1.html)

Description:
"Data - Statistics - Analysis of Variance" returns Err:508 for total of SS, while the direct DEVSQ formula returns correct result as expected.

Steps to reproduce:
1. Generate some numbers in some cells of column A and B, then select column A and B.
2. Go to "Data - Statistics - Analysis of Variance". "Results to" = "$D$1".
3. Click OK.

Current Behaviour:
"Total" of "SS" is "Err:508". However, when you input the same formula of that cell to another cell, it returns correct result.

You can open my test file for a better understanding of this issue. 
(Contents in D1:J11 are generated by "Data - Statistics - Analysis of Variance")

Reproduce with:
Win7 X64, version 4.2.5.2;
Win XP SP3, version 4.3.0.1
Fedora 20 x86_64, Version: 4.2.5.2 and 4.3.0.1.
Comment 1 Kevin Suo 2014-06-27 08:08:32 UTC
More useful info:
When the source data of Analysis of Variance is in the same column (for example, in column A), no error value will be generated.
Comment 2 kloud 2014-06-27 12:46:21 UTC
I confirm for 4.3 RC1, XP-32bit. If you copy / paste the cell with the Err:508-DEVSQ the pasted cell will produce the same error. If you copy the text of the cell and paste the formula into another cell or the same one, it will start showing the correct value.

I've switched this to major, since personally I'd use critical only for data loss, crashes etc.
Comment 3 Yousuf Philips (jay) (retired) 2014-06-27 15:16:44 UTC
Confirmed in 4.2.5 and 4.3.0 on Linux Mint. In 4.2.5, Error:508 also appears in SS, MS, F, and P-value of Between Groups.
Comment 4 Yousuf Philips (jay) (retired) 2014-06-27 15:17:10 UTC
Created attachment 101876 [details]
How my test file looked in 4.3
Comment 5 Yousuf Philips (jay) (retired) 2014-06-27 15:17:29 UTC
Created attachment 101877 [details]
How my test file looked in 4.2
Comment 6 Jacques Guilleron 2014-07-01 13:07:17 UTC
Hello Kevin,

If I copy A1:B100 in a new sheet and generate ANOVA in D1, I don't reproduce the issue. 
If now I select E11 and function wizard fx, I see the issue: Number 1 and Number 2 are together in the same input line, separated by a comma, wheras a semicolon is expected.
Input error?

Jacques
Comment 7 Kevin Suo 2014-07-01 15:51:47 UTC
(In reply to comment #6)

> If I copy A1:B100 in a new sheet and generate ANOVA in D1, I don't reproduce
> the issue. 
When you were generating ANOVA in D1, are you sure you are using A1:B100 as data source? (not only a single cell?)
I can 100% reproduce even I copy A1:B100 anywhere.

Those numbers in A1:A100 were generated by using the "Edit - Fill - Random Numbers".
In fact, even the following simple numbers in A1:B2 will get the same ERR 509:
1,2
3,4

> If now I select E11 and function wizard fx, I see the issue: Number 1 and
> Number 2 are together in the same input line, separated by a comma, wheras a
> semicolon is expected.

There is no problem with Number1 and Number2 separated by comma. In calc, formula both accepts "," and ";". For example, "=SUM(1,2)" and "=SUM(1;2)" all get the expected results.

> Input error?
The formula in E11 was not input manually, it was generated by "Data - Statistics - Analysis of Variance". So even there was "input error", its not the user's fault.
However, the reality is that, there is no input error. (If you copy and paste the exact same formula within E11 to another cell, it get the expected result, rather than ERR 508.)
Comment 8 Jacques Guilleron 2014-07-02 11:56:12 UTC
Hello Kevin,

Installed in a new profile, I don't reproduce the issue with LO 4.2.5.2 and Windows 7 Home Premium.
> When you were generating ANOVA in D1, are you sure you are using A1:B100 as    > data source? (not only a single cell?)
Yes. Dialog window at the beginning was made for that.
> I can 100% reproduce even I copy A1:B100 anywhere.
OK.
> Those numbers in A1:A100 were generated by using the "Edit - Fill - Random     > Numbers".
If I copied them, it's mainly to have the same results with ANOVA.
> In fact, even the following simple numbers in A1:B2 will get the same ERR 509:
> 1,2
> 3,4
OK.
> There is no problem with Number1 and Number2 separated by comma. In calc, 
> formula both accepts "," and ";". For example, "=SUM(1,2)" and "=SUM(1;2)" all > get the expected results.
I don't think so. "SUM(1,2)" give me "1,2", "SUM(1, 2)" give "Err:509" and delete the comma, and "SUM(1;2)" give "3".
Anyway, for ANNOVA, changing the comma by semicolon give the result into the formula of Total.
> The formula in E11 was not input manually, it was generated by "Data          > Statistics - Analysis of Variance". So even there was "input error", its not > the user's fault.
Yes. You are right and that has to be fixed.
> However, the reality is that, there is no input error. (If you copy and paste > the exact same formula within E11 to another cell, it get the expected       > result, rather than ERR 508.)
If I do that, I get Err:501

regards,

Jacques
Comment 9 Kevin Suo 2014-07-02 13:00:01 UTC
(In reply to comment #8)
Hi, Jacques. Thank you very much for your useful info. This bug is becoming really interesting.

* By default, LibreOffice uses "," as separator. (This can be verified when you reset your profile and go to "Tools - Options - LibreOffice Calc - Formula - Separators: Function".
* When I set the separator for function to ";" then follow the steps, I do not get the "Err:508". I think this may be why you do not reproduce this bug behaviour.
* When I set the separator back to ",", I can reproduce this bug behaviour again.

Also:
* When set separator to ",", "=SUM(1,2)" and "=SUM(1;2)" all get result 3. ("=SUM(1;2)" will be atomically converted to "=SUM(1,2)" when you hit ENTER)
* When set separator to ";", "=SUM(1,2)" results "#NAME?", and "=SUM(1;2)" results 3. 

I hope these information will be helpful. Thanks!
Comment 10 Kevin Suo 2014-07-02 13:03:44 UTC
(In reply to comment #9)

So I guess maybe libreoffice is using ";" as separator when applying "Data - Statistics - Analysis of Variance", even if the separator for calc is configured as ",", thus results error message.
Comment 11 Jacques Guilleron 2014-07-02 14:51:44 UTC
Well done! Kevin, you got it !

That's the right explanation. I forgot to look at there.
Another difficulty: those parameters are locale dependent.
If I set Locale to English(USA), I find comma for separator in functions and indeed this behaviour.
But I think this is also the case for some other functions.

It's already fine to have this.

Regards,

Jacques
Comment 12 Mirosław Zalewski 2014-11-16 11:19:22 UTC
Few code pointers for those interested. Fixing this should be easy enough for people with basic knowledge of C++ (not me, unfortunately).

sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx :
Total Sum of Squares in one factor ANOVA is constructed by using applyRangeList method (line 324). 
Withing groups sum of squares is constructed by using lclCreateMultiParameterFormula() function (line 296), and between groups sum of squares has semicolon hardcoded (line 251).

When output is written to spreadsheet, it's formula grammar is explicitly set to English (line 549).

But applyRangeList method, defined in sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx line 65, uses mpDoc->GetAddressConvention() to format formula according to local grammar (I assume).

This means that all formulas constructed using applyRangeList will return syntax error when local grammar is not compatible with English grammar. This affects only Total sum of squares of one factor ANOVA, but similar artifacts may be present in other statistical functions.
Comment 13 Mirosław Zalewski 2014-11-16 11:20:37 UTC
Hi Tomaz

There is a problem while constructing formulas for one factor ANOVA. It affects Chinese users (and probably others who use comma as formula argument separators). Very similar to bug 76731. Could you take a look at this?
Comment 14 Tomaz Vajngerl 2014-11-16 18:53:20 UTC
(In reply to Mirosław Zalewski from comment #13)
> Hi Tomaz
> 
> There is a problem while constructing formulas for one factor ANOVA. It
> affects Chinese users (and probably others who use comma as formula argument
> separators). Very similar to bug 76731. Could you take a look at this?

Yes, sure I can. :)
Comment 15 Kevin Suo 2015-06-23 09:37:58 UTC
This bug still exists with:

Version: 5.0.0.1
Build ID: 9a0b23dd0ab9652e0965484934309f2d49a7758e
Locale: zh-CN (zh_CN)
Comment 16 Kevin Suo 2015-12-03 05:28:45 UTC
Bug still exists in:
Version: 5.0.4.1 (x64)
Build ID: 2def61bcbb29a7a8611b833682fe1291910b11ad
Locale: zh-CN (zh_CN)

also exists in:
Version: 5.1.0.0.beta1-buildfix1 (x64)
Build ID: 13010a13177025f633c9b85adcb3edf6920e44e3
Threads 4; Ver: Windows 6.19; Render: GL; 
Locale: zh-CN (zh_CN)
Comment 17 fsshl 2015-12-23 10:39:17 UTC
in my trial(12/23/2015), a very similar problem on my 5.0.2 LibraOffice download, about calc to using its (tools->solver) to solve a simple nonlinear equation with one unknown; the correct answers have 2 roots(2 correct answers), 
if I enter init value close to one(answer of bigger value), calc's algorithm can converge to it; if I enter init value close to another one(answer of smaller value), calc's algorithm can converge to it too, however, at the end, calc popup
a "conclusion"-window which claimed (No Solution), and everything locked there unless I am be forced to click (OK), then that correct-smaller answer gone, back to original init value.  I wish there is one post here when it be solved at some newer version maybe.  Also, libreoffice 's calc algorithm spend a long time ~20-30 sec compare to another free-open-source on mathmatical calculation project, Octave, which can post out correct answers in 1 sec under my test.
Comment 18 Tomaz Vajngerl 2015-12-23 11:03:07 UTC
(In reply to fsshl from comment #17)
> in my trial(12/23/2015), a very similar problem on my 5.0.2 LibraOffice
> download, about calc to using its (tools->solver) to solve a simple
> nonlinear equation with one unknown; the correct answers have 2 roots(2
> correct answers), 
> if I enter init value close to one(answer of bigger value), calc's algorithm
> can converge to it; if I enter init value close to another one(answer of
> smaller value), calc's algorithm can converge to it too, however, at the
> end, calc popup
> a "conclusion"-window which claimed (No Solution), and everything locked
> there unless I am be forced to click (OK), then that correct-smaller answer
> gone, back to original init value.  I wish there is one post here when it be
> solved at some newer version maybe.  Also, libreoffice 's calc algorithm
> spend a long time ~20-30 sec compare to another free-open-source on
> mathmatical calculation project, Octave, which can post out correct answers
> in 1 sec under my test.

This has nothing to do with this bug... except that both are in Calc. Create a new bug and provide an example document.
Comment 19 QA Administrators 2017-09-01 11:17:01 UTC Comment hidden (obsolete)
Comment 20 Andrew Ziem 2018-02-22 17:46:48 UTC
This bug still happens. That is, when I create an ANOVA the Total SS has ERR 508 until I edit it.

LibreOffice version 6.0.0.3
Build ID 64a0f66915f38c6217de274f0aa8e15618924765
Ubuntu 16.04
Comment 21 raal 2018-06-10 11:45:47 UTC
LO 6.2master -  bug still happens in Total cell. It doesn't work in EN language settings, but works in CZ language settings (comma as decimal separator, ; as separator in functions)

Tomaz,if you find some time ... Thanks
Comment 22 raal 2018-06-10 11:46:26 UTC
Created attachment 142635 [details]
Calc 6.2master
Comment 23 Eike Rathke 2018-12-08 16:49:42 UTC
Taking.
Comment 24 Commit Notification 2018-12-08 20:19:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/470a91cb041782e29664212d096bb16873ba57a0%5E%21

Resolves: tdf#80583 create range list string with proper delimiter

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 25 Eike Rathke 2018-12-08 20:20:00 UTC
Pending review
https://gerrit.libreoffice.org/64823 for 6-2
https://gerrit.libreoffice.org/64824 for 6-1
Comment 26 Commit Notification 2018-12-08 21:31:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/740175916fd4cea821249201fa2af1fb2a3db867%5E%21

Resolves: tdf#80583 create range list string with proper delimiter

It will be available in 6.2.0.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 27 Commit Notification 2018-12-09 21:14:01 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

https://git.libreoffice.org/core/+/89d65f96128edf3055aaa5cebdaf78d893d01187%5E%21

Resolves: tdf#80583 create range list string with proper delimiter

It will be available in 6.1.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 28 Commit Notification 2018-12-10 15:23:46 UTC
Zdeněk Crhonek committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/4fc353ef9226c8f37f97a095db8f829cb10e609a%5E%21

uitest for bug tdf#80583

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 29 Commit Notification 2018-12-11 00:33:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1-4":

https://git.libreoffice.org/core/+/b8b2a7b85653aed203603f00287654cc62a053d7%5E%21

Resolves: tdf#80583 create range list string with proper delimiter

It will be available in 6.1.4.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 30 raal 2019-12-07 15:25:48 UTC
The test exist, set status to Verified.