Bug 74479 - 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.
Summary: CALC problem counting weekdays with sumproduct. Only Saturdays are not counte...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: Other other
: medium minor
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.1
Keywords: regression
: 74547 74677 75399 75425 (view as bug list)
Depends on:
Blocks: mab4.2
  Show dependency tree / graph
 
Reported: 2014-02-03 22:42 UTC by fwiubqpbvuebeqpnEX32nfi3vinkqjfG
Modified: 2015-01-22 22:00 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
CALC problem counting weekdays with sumproduct. Only Saturdays are not counted correctly when the selection contains empty cells. (64.18 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-03 22:42 UTC, fwiubqpbvuebeqpnEX32nfi3vinkqjfG 		Details
Simple file with SUMPRODUCT() error. (18.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-08 23:05 UTC, m_a_riosv 		Details
Simple file with SUMPRODUCT() error. (18.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-08 23:12 UTC, m_a_riosv 		Details
File sample with SUMPRODUCT() error. (20.47 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-02-08 23:33 UTC, m_a_riosv 		Details
Show Obsolete (2) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description fwiubqpbvuebeqpnEX32nfi3vinkqjfG 2014-02-03 22:42:23 UTC 
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.
Comment 1 m_a_riosv 2014-02-04 00:46:07 UTC 
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.
Comment 2 fwiubqpbvuebeqpnEX32nfi3vinkqjfG 2014-02-04 09:32:13 UTC 
(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.
Comment 3 m_a_riosv 2014-02-04 12:32:57 UTC 
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.
Comment 4 m_a_riosv 2014-02-08 23:02:13 UTC 
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.
Comment 5 m_a_riosv 2014-02-08 23:05:34 UTC 
Created attachment 93683 [details]
Simple file with SUMPRODUCT() error.

A simple file where to verify the issue.
Comment 6 m_a_riosv 2014-02-08 23:12:09 UTC 
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.
Comment 7 m_a_riosv 2014-02-08 23:14:19 UTC 
*** Bug 74547 has been marked as a duplicate of this bug. ***
Comment 8 m_a_riosv 2014-02-08 23:33:41 UTC 
Created attachment 93686 [details]
File sample with SUMPRODUCT() error.

I hope a more clear sample.
Comment 9 Kohei Yoshida 2014-02-09 14:31:21 UTC 
*** Bug 74677 has been marked as a duplicate of this bug. ***
Comment 10 Kohei Yoshida 2014-02-09 15:20:24 UTC 
(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.
Comment 11 Kohei Yoshida 2014-02-09 16:04:42 UTC 
I'm looking into it.
Comment 12 Kohei Yoshida 2014-02-09 16:15:37 UTC 
I think I got the gist of the problem, so no need to fix the test case.
Comment 13 Commit Notification 2014-02-09 16:31:42 UTC 
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.
Comment 14 Commit Notification 2014-02-09 16:31:59 UTC 
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.
Comment 15 Kohei Yoshida 2014-02-09 16:32:57 UTC 
4.2 backport request: https://gerrit.libreoffice.org/7954
Comment 16 Commit Notification 2014-02-09 17:18:22 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=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.
Comment 17 Kohei Yoshida 2014-02-09 17:46:59 UTC 
Fixed.
Comment 18 m_a_riosv 2014-02-09 18:35:46 UTC 
Thanks for fix it so fast. I'll try to check in a couple of days.
Comment 19 m_a_riosv 2014-02-11 02:34:11 UTC 
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
Comment 20 m_a_riosv 2014-02-23 15:11:05 UTC 
*** Bug 75399 has been marked as a duplicate of this bug. ***
Comment 21 Jacques Guilleron 2014-02-24 11:34:13 UTC 
*** Bug 75425 has been marked as a duplicate of this bug. ***