Download it now!
Bug 98297 - Array Formula Incorrectly Evaluated
Summary: Array Formula Incorrectly Evaluated
Status: VERIFIED 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.2.0 target:5.1.2 target:5.0.6
Keywords:
Depends on:
Blocks:
 
Reported: 2016-03-01 07:41 UTC by Luke
Modified: 2016-10-25 19:03 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Simple Array Formula (8.29 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-01 07:41 UTC, Luke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Luke 2016-03-01 07:41:41 UTC
Created attachment 123101 [details]
Simple Array Formula

This simple Array Formula evaluates to 4, when the correct result should be 3. Excel evaluates to 3. The example came from the tutorial, here:

http://www.myonlinetraininghub.com/excel-search-and-you-will-find
Comment 1 m.a.riosv 2016-03-02 00:23:14 UTC
I have not excel, maybe SEARCH() has a different return when text it's not founded, in calc returns #VALUE!.

{=COUNT(IF(ISERROR(SEARCH("brown";A1:A4));"";1))}

or

{=COUNT(IFERROR(SEARCH("brown";A1:A4);""))}

or

=SUMPRODUCT(NOT(ISERROR(SEARCH("brown";A1:A4))))

or

=SUMPRODUCT(IFERROR(SEARCH("brown";A1:A4)>0;0))

or 

{=COUNT(IFERROR(SEARCH("brown";A1:A4)>0;""))}

works for me, the interest of SUMPRODUCT() it's that is an implicit array, avoiding the need to enter the formula as array
Comment 2 Luke 2016-03-02 09:11:33 UTC
Eike,
Our functions should follow the same specification as Excel, right?
Comment 3 GerardF 2016-03-02 13:58:35 UTC
Excel function COUNT ignore errors values while Calc COUNT propagates errors.

See Excel COUNT : https://support.office.com/en-us/article/COUNT-function-a59cd7fc-b623-4d93-87a4-d23bf411294c

Not related to this bug, but function LOOKUP also ignore errors in Excel.
Comment 4 Eike Rathke 2016-03-02 17:29:08 UTC
Actually Calc also ignores errors in COUNT, just not in array mode ... to be fixed.

@Gerard:
What makes you think that LOOKUP ignores errors?
Comment 5 Commit Notification 2016-03-02 20:22:58 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#98297 exclude error values from COUNT in array/matrix

It will be available in 5.2.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 6 Eike Rathke 2016-03-02 21:50:05 UTC
Pending review
https://gerrit.libreoffice.org/22843 for 5-1
https://gerrit.libreoffice.org/22846 for 5-0
Comment 7 Commit Notification 2016-03-03 12:35:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

unit test for tdf#98297, exclude/include error values with COUNT/COUNTA

It will be available in 5.2.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 Luke 2016-03-04 00:37:57 UTC
The Array Formulas with count in the tutorial are all working now. Thanks Eike!
Comment 9 Commit Notification 2016-03-09 10:03:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6f718d779a99e86dc43444338d48c89cf64e7f9e&h=libreoffice-5-1

Resolves: tdf#98297 exclude error values from COUNT in array/matrix

It will be available in 5.1.2.

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 2016-03-09 10:16:59 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=84f8fa501ac772b40639d7b6e95ebeb752b01bf5&h=libreoffice-5-0

Resolves: tdf#98297 exclude error values from COUNT in array/matrix

It will be available in 5.0.6.

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 GerardF 2016-06-08 12:23:21 UTC
(In reply to Eike Rathke from comment #4)
> @Gerard:
> What makes you think that LOOKUP ignores errors?

Sorry to reply in a fixed bug, but this is the answer to your question :
https://bugs.documentfoundation.org/show_bug.cgi?id=100266