Issue 109264

Summary:

Unexpected #VALUE! error in LINEST

Product:

Calc

Reporter:

Andrea Pescetti <pescetti>

Component:

code

Assignee:

AOO issues mailing list <issues>

Status:

CONFIRMED ---

QA Contact:

Severity:

Trivial

Priority:

CC:

damark2209, issues, rb.henschel

Version:

OOo 3.2 RC5

Target Milestone:

---

Hardware:

Unknown

OS:

All

Issue Type:

DEFECT

Latest Confirmation in:

---

Developer Difficulty:

---

Attachments:

Description	Flags
In OOo, #VALUE!; saved to XLS, no problem in Excel	none
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.