After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 662551 - SUMPRODUCT
SUMPRODUCT
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: Analytics
git master
Other Linux
: Normal normal
: ---
Assigned To: Morten Welinder
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2011-10-23 20:25 UTC by Andreas J. Guelzow
Modified: 2011-11-01 17:26 UTC
See Also:
GNOME target: ---
GNOME version: ---


Description Andreas J. Guelzow 2011-10-23 20:25:35 UTC 
I have marked this as an analytics problem but it may just be a documentation problem.

In Gnumeric SUMPRODUCT performs implicit intersection when evaluating its arguments.

In OpenFormula/ODF SUMPRODUCT is specifically defined not to perform implicit intersection.

We need to figure out what happens in Excel.

One can test fort implict intersection by

A1: 1

C3: =SUMPRODUCT(A1:A128>0)

In Gnumeric with implicit intersection, C3 evaluates to 0.
In LibreOffice without implicit intersection, C3 evaluates to 1.

If Excel's behaviour matches Gnumeric's we should document the distinction between OpenFormula and Gnumeric.

If Excel's behaviour matches LibreOffice's we should consider changing Gnumeric's behaviour.
Comment 1 Andreas J. Guelzow 2011-10-23 20:37:14 UTC 
There might be some relationship with bug #533507.
Comment 2 Valek Filippov 2011-10-23 20:58:01 UTC 
Excel 2k7 gives me "0".
Comment 3 Andreas J. Guelzow 2011-10-23 21:37:36 UTC 
So, Excel also seems to use implicit intersection. We need to change the description of SUMPRODUCT to emphasize that it is not strictly ODF compatible.
Comment 4 Morten Welinder 2011-10-24 13:40:36 UTC 
Either that, or we grow a SUMPRODUCT.ODF
Comment 5 Andreas J. Guelzow 2011-10-24 14:39:54 UTC 
I would prefer an ODF.SUMPRODUCT (which is the format we are using if we would encounter a claimed ODF function that we do not recognize).
Comment 6 Andreas J. Guelzow 2011-10-24 16:59:19 UTC 
I guess, on second thought since the ODF. prefix is used for exactly that purpose an intentional function ought to be named differently, e.g. SUMPRODUCT.ODF.
Comment 7 Morten Welinder 2011-10-24 17:08:07 UTC 
ODF.SUMPRODUCT is fine.  We aren't going to have an overlap.
Comment 8 Morten Welinder 2011-10-31 17:42:08 UTC 
We now have ODF.SUMPRODUCT.

Note, that the above analysis is not right.  The differences between our
(Excel's) SUMPRODUCT and ODF.SUMPRODUCT is that the former ignores TRUE
while the latter considers it 1.

Leaving open for odf mapping on read.
Comment 9 Andreas J. Guelzow 2011-10-31 19:51:43 UTC 
mappings on read (and write) are done:

on read:

SUMPRODUCT              -> ODF.SUMPRODUCT
ORG.GNUMERIC.SUMPRODUCT -> SUMPRODUCT     (default mapping)

on write:

ODF.SUMPRODUCT          -> SUMPRODUCT
SUMPRODUCT              -> ORG.GNUMERIC.SUMPRODUCT (default mapping)
Comment 10 Andreas J. Guelzow 2011-11-01 08:50:24 UTC 
I have tracked down the initial issue:

A1:A4 has x,1,2,3
B1:B4 has y,1,2,3

C1 has =SUMPRODUCT(A1:A4*B1:B4) or =ODF.SUMPRODUCT(A1:A4*B1:B4)

Gnumeric yields #VALUE in C1. LibreOffice (for SUMPRODUCT) yields 14.

ODF/OpenFormula is pretty clear: unless specifically stated otherwise, if a function is given an error value as argument the result should be an error value. Clearly A1*A4 is an error so  the result according to ODF/OpenFormula should be an error. LibreOffice's result is therefore not in accordance with ODF/OpenFormula.

Note that the result for =SUMPRODUCT(A1:A4,B1:B4) or =ODF.SUMPRODUCT(A1:A4,B1:B4) is 14 in both Gnumeric and Libreoffice since both ignore the string values.
Comment 11 Andreas J. Guelzow 2011-11-01 17:26:11 UTC 
I filed a bug against LibreOffice: 
https://bugs.freedesktop.org/show_bug.cgi?id=42481