Apache OpenOffice (AOO) Bugzilla – Issue 109264
Unexpected #VALUE! error in LINEST
Last modified: 2018-11-07 19:27:32 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]
Created attachment 67819 [details] In OOo, #VALUE!; saved to XLS, no problem in Excel
Other errors with LINEST are in issues 112324 and 112447.
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.
Created attachment 86539 [details] Excel spreadsheet in question
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.