| Summary: | Array formula bug SUM(Len(A2:A200)) adds empty cells | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | elicoten |
| Component: | Calc | Assignee: | Kohei Yoshida <kohei> |
| Status: | VERIFIED FIXED | ||
| Severity: | normal | CC: | elicoten, miguelangelrv |
| Priority: | medium | ||
| Version: | 4.1.3.2 release | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | target:4.3.0 target:4.2.5 target:4.1.7 | ||
| Crash report or crash signature: | Regression By: | ||
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) I'll take this. This bug was already present in 4.0 in fact. 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. 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. Here is for 4.1: https://gerrit.libreoffice.org/9188 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. Fixed. 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. 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 |
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