Issue 109264 - Unexpected #VALUE! error in LINEST
Summary: Unexpected #VALUE! error in LINEST
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 3.2 RC5
Hardware: Unknown All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2010-02-14 22:08 UTC by Andrea Pescetti
Modified: 2018-11-07 19:27 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

In OOo, #VALUE!; saved to XLS, no problem in Excel (10.43 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-02-14 22:09 UTC, Andrea Pescetti
no flags Details
Excel spreadsheet in question (45.00 KB, application/x-ole-storage)
2018-11-07 19:20 UTC, damark2209
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
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.