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.
Reproduced with LOdev 3.5.0beta2 4ca392c-760cc4d-f39cf3d-1b2857e-60db978 Ubuntu 10.04.3 x86 Linux 2.6.32-37-generic Russian UI
(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?
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.
Formula in E1 is not the best way. (locale dependant) :( Test both results C1 and D1 with ISERROR and ISTEXT.
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?
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.
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.
Pending review https://gerrit.libreoffice.org/15072 for 4-4 https://gerrit.libreoffice.org/15073 for 4-3
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.
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.
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.
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.
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.
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.
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.
This appears to have also changed the behaviour of SUM(), as described at https://bugzilla.redhat.com/show_bug.cgi?id=1219287
It did not, see explanation in https://bugzilla.redhat.com/show_bug.cgi?id=1219287#c6
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.
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?
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.
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.