Bug 42481 - Error value not propagated in array/matrix (e.g. incorrect SUMPRODUCT result)
Summary: Error value not propagated in array/matrix (e.g. incorrect SUMPRODUCT result)
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.4 release
Hardware: x86 (IA32) All
: high normal
Assignee: Eike Rathke
URL:
Whiteboard: odf target:4.5.0 target:4.4.3 target:...
Keywords:
Depends on:
Blocks:
 
Reported: 2011-11-01 10:25 UTC by Andreas J Guelzow
Modified: 2021-03-03 22:47 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
sumproduct test with array and error result (12.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-03-02 12:23 UTC, GerardF
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas J Guelzow 2011-11-01 10:25:21 UTC
A1:A4 has x,1,2,3
B1:B4 has y,1,2,3

C1:C4 has =A1*B1,...,=A4*B4  (which shows as #VALUE!,1,4,9)
        
E6 has =SUMPRODUCT(A1:A4*B1:B4)
E7 has =SUMPRODUCT(C1:C4)


E6 and E7 should show the same value since the result of A1:A4*B1:B4 is the same as C1:C4 (you could also enter the result of A1:A4*B1:B4 as an array in C1:C4 and observe the same).

The correct value for these SUMPRODUCT values is #VALUE! since ODF/OPENFORMULA (ODF 1.2, part 2, 6.1) requires SUMPRODUCT to return an error when a value provided to it is an error. 

E6 incorrectly shows 14.
Comment 1 tester8 2012-01-04 12:27:33 UTC
Reproduced with

LOdev 3.5.0beta2 
4ca392c-760cc4d-f39cf3d-1b2857e-60db978
Ubuntu 10.04.3 x86
Linux 2.6.32-37-generic Russian UI
Comment 2 Takeshi Abe 2012-03-02 02:00:37 UTC
(In reply to comment #0)
> E6 has =SUMPRODUCT(A1:A4*B1:B4)
Reproduced. Anyway just nitpicking, it means rather "=SUMPRODUCT(A1:A4,B1:B4)" or "=SUMPRODUCT(A1:A4;B1:B4)", doesn't it?
Comment 3 GerardF 2012-03-02 12:23:15 UTC
Created attachment 57947 [details]
sumproduct test with array and error result

I've made test like in comment 1 and reproduce.

Result 14 for SUMPRODUCT(A1:A4*B1:B4) is not directly a SUMPRODUCT bug but due to array formula.
Array formula returns "text" #VALUE ! instead of error result.
See attachment.

Hope this helps.
Comment 4 GerardF 2012-03-02 13:30:03 UTC
Formula in E1 is not the best way. (locale dependant) :(

Test both results C1 and D1 with ISERROR and ISTEXT.
Comment 5 Luke 2015-03-28 02:18:18 UTC
In LO Calc 4.5, C5, D5, D10, and D11 still all contain the wrong result of 14.

This bug has been partially fixed in OpenOffice 4.2.  In OpenOffice, D10 and C5 both give the correct result of "#VALUE!"

OpenOffice does yield a result of 14 for D12, but Excel 2013 also gives the same value. Is that correct?
Comment 6 Eike Rathke 2015-03-30 17:52:28 UTC
Result 14 in D12 is correct though a bit tricky.. One could argue that it should be the same as D11 (if #VALUE!), but on the other hand the range references can be converted to a NumberSequence each where strings are ignored before being forced to array, which apparently is what Excel does and we strive to do the same.
Comment 7 Commit Notification 2015-03-30 17:54:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#42481 propagate errors as errors in matrix calculations

It will be available in 4.5.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 8 Eike Rathke 2015-03-30 18:03:31 UTC
Pending review
https://gerrit.libreoffice.org/15072 for 4-4
https://gerrit.libreoffice.org/15073 for 4-3
Comment 9 Commit Notification 2015-03-31 15:51:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

use error value instead of string in array/matrix, tdf#42481 related

It will be available in 4.5.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 10 Commit Notification 2015-04-01 07:31:15 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0f5cbcc5bd5fbde8f13a6655bd47dca4d7722ce9&h=libreoffice-4-4

Resolves: tdf#42481 propagate errors as errors in matrix calculations

It will be available in 4.4.3.

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 11 Commit Notification 2015-04-01 07:36:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c2288a0732f2a10ba4590f3dace00294db389150&h=libreoffice-4-3

Resolves: tdf#42481 propagate errors as errors in matrix calculations

It will be available in 4.3.7.

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 12 Commit Notification 2015-04-01 08:09:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bf8fdf104b780b8bf54710d00cf3f19922700fdf&h=libreoffice-4-4

use error value instead of string in array/matrix, tdf#42481 related

It will be available in 4.4.3.

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 13 Commit Notification 2015-04-01 23:32:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

use error value instead of string in array/matrix, tdf#89387 tdf#42481 related

It will be available in 4.5.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 14 Commit Notification 2015-04-02 21:37:11 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

empty element evaluates to 0, tdf#89387 tdf#42481 related

It will be available in 4.5.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 15 Commit Notification 2015-04-13 20:07:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6b3decb3bb0a580c2978028660567ba3a66878ae&h=libreoffice-4-4

use error value instead of string in array/matrix, tdf#42481 related

It will be available in 4.4.3.

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 16 David Woodhouse 2015-05-07 15:48:34 UTC
This appears to have also changed the behaviour of SUM(), as described at https://bugzilla.redhat.com/show_bug.cgi?id=1219287
Comment 17 Eike Rathke 2015-06-01 17:27:37 UTC
It did not, see explanation in https://bugzilla.redhat.com/show_bug.cgi?id=1219287#c6
Comment 18 Eike Rathke 2015-06-01 18:24:03 UTC
See also https://bugs.documentfoundation.org/show_bug.cgi?id=91453#c5
Configuration of "treat empty strings as zero" should be considered also for matrix operations.
Comment 19 Luke 2015-06-01 23:56:49 UTC
Eike, 
After your patch in attachment 57947 [details], C5 and D10 are still showing a value of 14, while Excel and OpenOffice are showing "#VALUE!". Has this issue been fully resolved?
Comment 20 Luke 2015-06-02 00:06:57 UTC
Eike,
Please ignore my last comment. Shift+Ctrl+F9 in LO Calc fixes the dependency. Excel/OpenOffice must recalculate by default while LibreOffice does not.
Comment 21 Commit Notification 2021-03-03 22:47:18 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/722ec600e85cca2e94e82e69f8d13773061172b9

tdf#42481: sc_subsequent_filters: Add unittest

It will be available in 7.2.0.

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

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