Bug 73374 - EDITING: R^2 calculation of trendline with forced intercept is not correct
Summary: EDITING: R^2 calculation of trendline with forced intercept is not correct
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
Version:
(earliest affected)
4.2.0.1 rc
Hardware: Other All
: medium normal
Assignee: Laurent Balland
URL:
Whiteboard: BSA target:4.3.0 target:4.2.1
Keywords:
Depends on:
Blocks:
 
Reported: 2014-01-07 22:11 UTC by Laurent Balland
Modified: 2014-02-27 10:50 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Article with detail of calculation in case of forced intercept (81.76 KB, application/pdf)
2014-01-07 22:11 UTC, Laurent Balland
Details
Test file showing R^2 value with forced intercept with LINEST and trendline (36.36 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-01-07 22:19 UTC, Laurent Balland
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Laurent Balland 2014-01-07 22:11:42 UTC
Created attachment 91622 [details]
Article with detail of calculation in case of forced intercept

Problem description: 

Steps to reproduce:
1. Enter data: X=1, 2, 3 and Y=11, 12, 13
2. Select data and create XY chart
3. Insert a linear trend line with forced intercept and show R^2

Current behavior:
R^2=0 because it is calculated with the same formula as with free intercept and gives a negative value, like Excel 2003 does.

Expected behavior:
R^2 should not be calculated with the same formula in case of forced intercept. Attached article give the correct formula in case of forced intercept: (4') instead of (4)

              
Operating System: All
Version: 4.2.0.1 rc
Comment 1 Laurent Balland 2014-01-07 22:19:26 UTC
Created attachment 91623 [details]
Test file showing R^2 value with forced intercept with LINEST and trendline

Moreover, LINEST function gives the correct value of R^2.
I attach an example.
Comment 3 Laurent Balland 2014-01-09 07:50:58 UTC
Take.
Propose commit
https://gerrit.libreoffice.org/#/c/7326/
Comment 4 Jean-Baptiste Faure 2014-01-11 18:26:07 UTC
It's clear that both LINEST() function and trendline must give the same R^2 value.
So set status to NEW.

Best regards. JBF
Comment 5 Commit Notification 2014-02-07 12:02:15 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e67015be6e9e53e19466117d692de17c18463da

fdo#73374 Trendline: correct R^2 for forced intercept



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 6 Tomaz Vajngerl 2014-02-07 12:04:35 UTC
Hi Laurent, 

I finally had the time to review you patch. Can you also prepare a patch for LO 4.2?

Regards, Tomaž
Comment 7 Commit Notification 2014-02-10 08:05:32 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bac928cf6405c869ab83bc136dac52b037386108&h=libreoffice-4-2

fdo#73374 Trendline: correct R^2 for forced intercept


It will be available in LibreOffice 4.2.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 8 Kohei Yoshida 2014-02-16 22:16:01 UTC
Fixed, right?
Comment 9 Laurent Balland 2014-02-17 08:40:26 UTC
(In reply to comment #8)
> Fixed, right?

+1

Laurent BP