Re: sumproduct with "names" // fine in libreoffice, #VALUE! warning in gnumeric

On Sun, 2011-10-23 at 11:07 +0200, fellowsgarden gmail com wrote:

I just installed gnumeric (on ubuntu) for the first time and tried to
open a document from libreoffice calc.

Gnumeric returns an error message for this field, when opening the


The cell shows the #VALUE! warning.

Both in openoffice / libreoffice this works, and is supposed to return
the sum of all expenses _excluding_ rent (which it does, in oo &

Gnumeric does recognize the sumproduct function. It, too, recognizes
the "names" I defined (it shows a rectangle around them, resp., when I
F2 the problematic field).

I don't see the error message, but since Gnumeric does not support
password protected ODF files,I had to open the file in LibreOffice and
resave it without password. The resulting file doe snot show an error.

I still see the difference in value with respect to the SUMPRODUCT
As documented in Gnumeric's description of SUMPRODUCT, Gnumeric uses
implicit intersection for its arguments. LibreOffice does not.

Note that SUMPRODUCT typically adds the product of corresponding terms.
IN this case the spreadsheet passes only a single expression to
SUMPRODUCT so in fact the calculation is equivalent to SUM except that
in LibreOffice SUM also performs implicit intersection. So SUM is used
instead of SUMPRODUCT to avoid implicit intersection. There are other
ways of writing this calculation which does not depend on the implicit
intersection vs non-implicit intersection situation.

I have filed this as


Andreas Guelzow <aguelzow pyrshep ca>

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]