Issue 109264

Summary: Unexpected #VALUE! error in LINEST
Product: Calc Reporter: Andrea Pescetti <pescetti>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: damark2209, issues, rb.henschel
Version: OOo 3.2 RC5   
Target Milestone: ---   
Hardware: Unknown   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
In OOo, #VALUE!; saved to XLS, no problem in Excel
Excel spreadsheet in question none

Description Andrea Pescetti 2010-02-14 22:08:26 UTC
The Calc file I will attach shortly contains a 5x2 matrix, with red background,
showing  wrong results (actually, #VALUE! errors) given by LINEST().

Reportedly, if you save the file in XLS format and open it with Microsoft Excel,
you are given sensible results and no errors.

Maybe a numerical stability problem?

[Reported by Antonio Ricci during Italian QA tests]
Comment 1 Andrea Pescetti 2010-02-14 22:09:56 UTC
Created attachment 67819 [details]
In OOo, #VALUE!; saved to XLS, no problem in Excel
Comment 2 Regina Henschel 2010-06-21 20:14:07 UTC
Other errors with LINEST are in issues 112324 and 112447.
Comment 3 Regina Henschel 2010-07-08 16:11:54 UTC
For the given data you get Y=0.0011*X+0.11. Therefore all residuals have to be
zero and the standard errors are zero too. The F-statistic should result in an
error because of division by zero.

It is wrong in Excel, which calculates a F-statistic. Gnumeric gets an error for
the F-statistic, which seems appropriate to me, but has non zero errors too.

If I calculate first the means and then the coefficients and errors, I get
zeros. So I think, that an improvement is possible, if we change from one
iteration to two iterations as we had done in other statistical functions.
Comment 4 damark2209 2018-11-07 19:20:05 UTC
Created attachment 86539 [details]
Excel spreadsheet in question
Comment 5 damark2209 2018-11-07 19:27:32 UTC
Exact issue on 7 Nov 2018 with attached spreadsheet created in Excel. When opened in Open Office Calc as either xls or ods simple formula results show as #VALUE!

When file opened in Excel, results are correct but several cells have notation that entries were made as text. These correspond to the #VALUE! results, so I assume Calc cannot compute these.