| Summary: | CALC problem counting weekdays with sumproduct. Only Saturdays are not counted correctly when the selection contains empty cells or no date. SUMPRODUCT() result error with functions or calculations the last parameter. | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | fwiubqpbvuebeqpnEX32nfi3vinkqjfG |
| Component: | Spreadsheet | Assignee: | Kohei Yoshida (inactive) <libreoffice> |
| Status: | RESOLVED FIXED | QA Contact: | |
| Severity: | minor | ||
| Priority: | medium | CC: | fdbugs, Herring.Choker, karlooforum, laszlovaly, mariosv |
| Version: | 4.2.0.4 release | Keywords: | regression |
| Hardware: | Other | ||
| OS: | other | ||
| Whiteboard: | target:4.3.0 target:4.2.1 | ||
| i915 platform: | i915 features: | ||
| Bug Depends on: | |||
| Bug Blocks: | 65675 | ||
| Attachments: |
CALC problem counting weekdays with sumproduct. Only Saturdays are not counted correctly when the selection contains empty cells.
Simple file with SUMPRODUCT() error. Simple file with SUMPRODUCT() error. File sample with SUMPRODUCT() error. |
||
Hi @fwiubqpbvuebeqpnEX32nfi3vinkqjfG, thanks for reporting. =WEEKDAY(0;2) = 6 => Saturday Date for 0 is 30/12/1899 => Saturday same result with an empty cell as argument. You need to verify in the SUMPRODUCT() function that the cells are not empty. e.g. B21: =SUMPRODUCT(WEEKDAY($A$20:$A$22;2)=6;NOT(ISBLANK($A$20:$A$22))) For me there is not a bug. (In reply to comment #1) > Hi @fwiubqpbvuebeqpnEX32nfi3vinkqjfG, thanks for reporting. > > =WEEKDAY(0;2) = 6 => Saturday > Date for 0 is 30/12/1899 => Saturday > same result with an empty cell as argument. > > You need to verify in the SUMPRODUCT() function that the cells are not empty. > e.g. > B21: =SUMPRODUCT(WEEKDAY($A$20:$A$22;2)=6;NOT(ISBLANK($A$20:$A$22))) > > For me there is not a bug. Hi! Thanks for the fast reply. You are right that the result is correct if the selection is ok. This is my case (A) in the example. If you followed cases (B) and (C) in the example, you see there is something wrong. Because results are even fine on Monday, Tuesday, Wednesday, Thursday, Friday and Sunday when the selection(s) contain empty cells, but why not for Saturdays? In older LibreOffice versions i had really a problem, because i needed to select Saturdays of hundreds of cells and it gives me wrong results even when the selection was ok. So for me it is a small bug. Please read again my first comment. With an empty cell the result for WEEKDAY(emptycell;2) is always Saturday, an empty cell is zero for WEEKDAY(), you need verify it is not an empty cell to avoid the issue as is made in the SUMPRODUCT() formula in my previous comment. Hi fwiubqpbvuebeqpnEX32nfi3vinkqjfG, in this thread I have found a similar ussue: http://en.libreofficeforum.org/node/7523#comment-30361 Seems that when there is function in the last parameter of SUMPRODUCT(), empty cells in one of the ranges are taken as 1. In your sample file: B21: =SUMA.PRODUCTO((DÍASEM($A$20:$A$22;2)=6)*1;A20:A22) => 0 B21: =SUMA.PRODUCTO(A20:A22;(DÍASEM($A$20:$A$22;2)=6)*1) => 3 So sorry you are in reason a bug is there. Created attachment 93683 [details]
Simple file with SUMPRODUCT() error.
A simple file where to verify the issue.
Created attachment 93685 [details]
Simple file with SUMPRODUCT() error.
The issue is not only with a function in the last operator, but with any calculation in the last parameter.
*** Bug 74547 has been marked as a duplicate of this bug. *** Created attachment 93686 [details]
File sample with SUMPRODUCT() error.
I hope a more clear sample.
*** Bug 74677 has been marked as a duplicate of this bug. *** (In reply to comment #8) > Created attachment 93686 [details] > File sample with SUMPRODUCT() error. > > I hope a more clear sample. mariosv, there is something wrong with your attachment, I'm afraid. Firefox says it has error, and Calc tries to open it as csv. I'm looking into it. I think I got the gist of the problem, so no need to fix the test case. Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=71fe47cfe652829ff7dc09ae49b1c6c22d9b6a6d fdo#74479: Test case for SUMPRODUCT. 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=295869ce95c00a0e0b192ea6bf62753f91badaf2 fdo#74479: Treat empty cells as if they have a value of 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. 4.2 backport request: https://gerrit.libreoffice.org/7954 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=dbb89000146d118ea28f0869d03e121d01e11f04&h=libreoffice-4-2 fdo#74479: Treat empty cells as if they have a value of 0. It will be available in LibreOffice 4.2.1. 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. Thanks for fix it so fast. I'll try to check in a couple of days. Verified fixed.
Version: 4.2.1.0.0+ Build ID: 684a0308dcb847f816967202ba87f536b83dcb4e
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-02-10_23:09:14
*** Bug 75399 has been marked as a duplicate of this bug. *** *** Bug 75425 has been marked as a duplicate of this bug. *** |
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.
Created attachment 93327 [details] CALC problem counting weekdays with sumproduct. Only Saturdays are not counted correctly when the selection contains empty cells. CALC problem counting weekdays with sumproduct. Only Saturdays are not counted correctly when the selection contains empty cells or no date. It looks like only while counting Saturdays the number of empty cells in the range (C=34-20=14) is added to the expected result (2). So we get 16 as wrong result. Version: 4.2.0.4 Build-ID: 05dceb5d363845f2cf968344d7adab8dcfb2ba71 Previous versions have this bug too running on windows 64-bit Windows 7.