Description: When plotting a scatter plot and forcing a trendline through zero, the R^2 value is wrong. I created a screenshot as an example: In Libreoffice Calc you get those lines and values: https://i.imgur.com/9ZMpKrV.png The R^2 value of the forced trendline is 0.95 and a lot better than the R^2 value of the unforced line (0.779) I plotted the same thing in Microsoft excel:https://i.imgur.com/tdYMbQL.png As you can see the equations are the same, the R^2 value for the unforced line too, but excel calculates R^2 with the forced trendline correctly Steps to Reproduce: 1. Create Scatter Plot 2. Fit a trendline and force it through zero 3. Compare to eg Microsoft excel results 4. See that Libreoffice calc has the wrong value Actual Results: Results: R^2 is wrong Expected Results: Expected: Correct R^2 value Reproducible: Always User Profile Reset: Yes Additional Info:
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.) I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Created attachment 154749 [details] Example of wrongl R^2 calculation Sample file as requested. The correct R^2 value is also included in the file.
Created attachment 157556 [details] Screenshot of error in 6.4 Bug is still present. It really is a big problem as trend lines and R^2 values are widely used!
I confirm R^2 value is not 0,9373 in the lower graph
I had a look in the code, this is deliberate. A specific computation was introduced to reproduce the behavior of the LINEST function, by replacing the difference to the average by the difference to the constant to intercept: * The original bug is here: https://bugs.documentfoundation.org/show_bug.cgi?id=73374 * The commit made to solve it is here: https://github.com/LibreOffice/core/commit/9e67015be6e9e53e19466117d692de17c18463da The LINEST function also gives R2=0.985148. In Excel (Office365), the LINEST function answers 0.985148 and the chart R2 is 0.9373.
(In reply to Leyan from comment #5) > I had a look in the code, this is deliberate. A specific computation was > introduced to reproduce the behavior of the LINEST function, by replacing > the difference to the average by the difference to the constant to intercept: > > * The original bug is here: > https://bugs.documentfoundation.org/show_bug.cgi?id=73374 > * The commit made to solve it is here: > https://github.com/LibreOffice/core/commit/ > 9e67015be6e9e53e19466117d692de17c18463da > > The LINEST function also gives R2=0.985148. > > In Excel (Office365), the LINEST function answers 0.985148 and the chart R2 > is 0.9373. Laurent: can you comment on this?
(In reply to Buovjaga from comment #6) > Laurent: can you comment on this? Excel chart is wrong ;-) Scientific article Attachment 91622 [details] of bug 73374 explain why Excel calculation is wrong: it can give negative R² In case of forced intercept, a different calculation must be used, as LINEST function does (in LibO and Excel).
Thank you for taking a look and linking to the paper. Turns out my excel using colleague have wrong numbers. This is still unexpected behavior but als libreoffice is mathematically correct there is nothing that can be done. Thanks again, I will read up on the mathematics and correct my calculations. :)