Bug 75397 - Array formula bug SUM(Len(A2:A200)) adds empty cells
Summary: Array formula bug SUM(Len(A2:A200)) adds empty cells
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.3.2 release
Hardware: All All
: medium normal
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.5 target:4.1.7
Keywords:
Depends on:
Blocks:
 
Reported: 2014-02-23 11:44 UTC by elicoten
Modified: 2014-05-04 09:09 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description elicoten 2014-02-23 11:44:58 UTC
In a sheet where the range A2:A200 contain text strings and some are empty, the array formula
{=SUM(Len(A2:A200))} adds one to the sum for every empty cell.

{=SUM(IF(ISBLANK(A2:A200),0,LEN(A2:K200)))}, however, works correctly - but the if(isblank()) shouldn't be necessary.

It seems to be more than just the SUM function, as I tried the QUARTILE function with LEN as an array function and I also got the result 1 when I was expecting 0. So perhaps any time you do an array function LEN() with empty cells, LEN gives 1 when it should give 0.

(Needless to say the nonarray variation where you use a range of cells to perform the same calculation e.g.
=LEN(A2)
=LEN(A3)
=LEN(A4)
=LEN(A5)
...
=LEN(A199)
=LEN(A200)
=SUM(B2:B200)

works fine.

See demonstration of the problem in cell B12 here: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1273659/+attachment/3962696/+files/Array%20Formula%20Bug.ods
Comment 2 m_a_riosv 2014-02-23 15:23:37 UTC
Hi elicoten, thanks for reporting

Reproducible with:
Win7x64Ultimate
Version: 4.1.6.0.0+ Build ID: 1c33633ef18274bf384c74c492195519be83c05
until
Version: 4.3.0.0.alpha0+
Build ID: d84ccb39b744457cd47125beb4291c84223d5219
TinderBox: Win-x86@39, Branch:master, Time: 2014-02-22_10:05:06

Last working:
Versión 3.6.7.2 (ID de compilación: e183d5b)
Comment 3 Kohei Yoshida 2014-04-28 15:51:11 UTC
I'll take this.  This bug was already present in 4.0 in fact.
Comment 4 Kohei Yoshida 2014-04-28 15:59:45 UTC
4.2: https://gerrit.libreoffice.org/9187
Comment 5 Commit Notification 2014-04-28 15:59:55 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#75397: Write unit test for this.



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 Commit Notification 2014-04-28 16:00:08 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

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

fdo#75397: Return an empty string for empty element.



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 Kohei Yoshida 2014-04-28 16:06:00 UTC
Here is for 4.1: https://gerrit.libreoffice.org/9188
Comment 8 Commit Notification 2014-04-28 22:56:18 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#75397: Return an empty string for empty element.


It will be available in LibreOffice 4.2.5.

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 9 Kohei Yoshida 2014-04-28 23:05:11 UTC
Fixed.
Comment 10 Commit Notification 2014-04-29 00:34:33 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

fdo#75397: Return an empty string for empty element.


It will be available in LibreOffice 4.1.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 11 m_a_riosv 2014-05-04 02:29:01 UTC
Thanks Kohei,
Verified with:
Win7x64Ultimate
Version: 4.2.5.0.0+ Build ID: 59906c3d54e6541185f4bf85b1d1c70530198059
   TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-04-30_09:30:13
Version: 4.3.0.0.alpha1+ Build ID: 20778b037c688759a4dc46acb8aeb66c9d1290cc
   TinderBox: Win-x86@39, Branch:master, Time: 2014-05-03_23:16:12