Bug Hunting Session
Bug 94796 - LINEST and LOGEST should have ForceArray parameter types (was: LINEST and INDEX problem)
Summary: LINEST and LOGEST should have ForceArray parameter types (was: LINEST and IN...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0 target:5.0.4
Keywords:
Depends on:
Blocks:
 
Reported: 2015-10-05 17:58 UTC by mas
Modified: 2016-10-25 19:21 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
calc file showing the unexpected behavior of Index and Linest (10.49 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-06 11:14 UTC, mas
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mas 2015-10-05 17:58:22 UTC
There seems to be a problem using INDEX with LINEST.

For example, suppose we have the following array:
{7,100;9,105;11,104;12,108;15,111;17,120;19,133}
occupying A1:B7

Consider the two examples using linest using LINEST:
method 1:  LINEST(B1:B7,A1:A7)
    yields an array (1 by 2)=> {2.442, 80.177}
method 2:  LINEST(B1:B7,A1:A7^{1,2})
    yields an array (1 by 3) => {0.238, -3.768, 116.906}

These work as expected.  However, if I try to do the following with the aforementioned examples:
method 1a: =INDEX(LINEST(B1:B7,A1:A7),1,1)
   yields the expected value of 2.442
method 1b: =INDEX(LINEST(B1:B7,A1:A7^{1,2}),1,1)
   yields an error Err:502  (should be 0.238)
Comment 1 mas 2015-10-06 11:14:35 UTC
Created attachment 119347 [details]
calc file showing the unexpected behavior of Index and Linest
Comment 2 GerardF 2015-10-06 13:37:52 UTC
Hi,

In the second case, you need to enter the formula as an array formula (validation with Ctrl+Shift+Enter) to force array calculation of this part of the formula A1:A7^{1,2}

INDEX expect an array in his 1st parameter and force array calculation of LINEST in the 1st case but not in the second.

I don't know if this is really a bug or not...
I let this UNCONFIRMED waiting for other opinion.
Comment 3 mas 2015-10-06 14:39:28 UTC
If the formula is entered as an array INDEX result is an array, not a value.
I would think that this is not the expected behavior.  Also, for what it is worth, this works as expected in Excel.
Comment 4 GerardF 2015-10-06 16:05:54 UTC
(In reply to mas from comment #3)
> If the formula is entered as an array INDEX result is an array, not a value.
> I would think that this is not the expected behavior.

No. INDEX(Linest_array,1,1) returns a singleton, not an array.

> Also, for what it is worth, this works as expected in Excel.

As per this, set it to NEW.
Even if ODF1.2 norms do no provide a "forcearray" in first arg of INDEX.

Add a dev to this bug.
@ Eike,
Sorry if I add you in CC as this is not how bugreport should work, but I need your opinion about this.
Comment 5 Eike Rathke 2015-10-07 20:05:09 UTC
The error already stems from the LINEST() function and is only propagated through INDEX().

With LINEST(B1:B7,A1:A7^{1,2}) in non-array mode the arrays passed to LINEST currently are:
knownX: 1 column * 7 rows
knownY: 2 columns * 1 row

This does not meet the constraints of LINEST:
(COLUMNS(knownY) = COLUMNS(knownX) and ROWS(knownY) = ROWS(knownX))  or (COLUMNS(knownY) = 1 and ROWS(knownY) = ROWS(knownX)) or (COLUMNS(knownY) = COLUMNS(knownX) and ROWS(knownY) = 1)

See http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#LINEST

In array mode the arrays passed are:
knownX: 1 column * 7 rows
knownY: 2 columns * 7 rows

which meets the constraints.

If Excel does not yield an error in the non-array case, my understanding is that LINEST and LOGEST in ODF OpenFormula (ODFF) should have ForceArray types instead of Array.
Comment 6 Commit Notification 2015-10-07 20:27:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f3dfab0c23bde81a00fa2f16cf4d96c8809e5045

Resolves: tdf#94796 LINEST and LOGEST have ForceArray parameters

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Eike Rathke 2015-10-07 20:44:22 UTC
Pending review https://gerrit.libreoffice.org/19237 for 5-0
Comment 8 Commit Notification 2015-10-09 10:20:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=70f5afe3707c155b412e05e9750c38d00b56d345&h=libreoffice-5-0

Resolves: tdf#94796 LINEST and LOGEST have ForceArray parameters

It will be available in 5.0.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.