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)
Created attachment 119347 [details] calc file showing the unexpected behavior of Index and Linest
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.
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.
(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.
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.
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.
Pending review https://gerrit.libreoffice.org/19237 for 5-0
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.