Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Unexpected #VALUE! error in LINEST|
|Product:||Calc||Reporter:||Andrea Pescetti <pescetti>|
|Component:||code||Assignee:||AOO issues mailing list <issues>|
|Status:||CONFIRMED ---||QA Contact:|
|Priority:||P3||CC:||damark2209, issues, rb.henschel|
|Version:||OOo 3.2 RC5|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
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.