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

[Andreas Guelzow <aguelzow pyrshep ca>]

On Sun, 2011-10-23 at 11:07 +0200, fellowsgarden gmail com wrote:
> Hello!
>
>
> 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
> document:
>
>
> =sumproduct((item_temp<>"rent")*(amount_temp<0)*amount_temp)
>
>
> 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 &
> libreoffice).
>
>
> 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
function:
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  https://bugzilla.gnome.org/show_bug.cgi?id=662551

Andreas
>

-- 
Andreas Guelzow <aguelzow pyrshep ca>