Indirect and trend functions in Gnumeric
Jim Martin
I have just downloaded the windows version of gnumeric and I am having a
little trouble with a formula that includes trend and indirect functions.
Here is the formula that I use in excel:
=trend(indirect("m"&$E3):indirect("m"&$F3),indirect("l"&$E3):indirect("l"&$F
3),$B3,TRUE)
This works fine in excel but gives me #VALUE! in gnumeric. I have tried
various forms as suggested in the help such as using [, instead of just ,
but nothing seems to help.
Anybody? Jody? Harlan?
Thanks,
Jim
hrl...@aol.com
While this formula doesn't need to be entered as an array formula in
Excel, it appears you must enter this formula as an array formula in
Gnumeric. I entered 1..10 in A1:A10, =A1^2 in B1 and filled B1 down
into B2:B10, entered 2 in D1 and 5 in D2, and the array formula
=trend(indirect("B"&D1):indirect("B"&D2),indirect("A"&D1):indirect("A"&D2))
returns 3, the same result as =trend(B2:B5,A2:A5), which also had to be
entered as an array formula.
Note also that Gnumeric supports conditional counting and summing using
SUMPRODUCT without needing to enter such formulas as array formulas. As
an added bonus (DON'T DO THIS IF YOU NEED TO USE WORKBOOKS IN BOTH
GNUMERIC AND EXCEL!) Gnumeric automatically converts TRUE/FALSE to 1/0
in SUMPRODUCT, so the Excel formula
=SUMPRODUCT(--(A1:A10>5),B1:B10)
could be replaced with the more obvious (well, to me)
=sumproduct(A1:A10>5,B1:B10)
in Gnumeric. My only complaint about this is that if I enter
=sumproduct((A1:A10>5),B1:B10)
Gnumeric replaces this with
=sumproduct(A1:A10>5,B1:B10)
eating the unnecessary parentheses around the conditional expression.
I'd prefer Gnumeric retained such parentheses. They may not be
redundant in terms of formula parsing, but for some like me they make
it clearer to distinguish the conditional terms from the values.
Jim Martin
Whew.... thanks Harlan. I will need to chew on this for a while. Thanks for
taking the time to lay it all out for me!
Cheers,
Jim
<hrl...@aol.com> wrote in message
news:1104866142.3...@f14g2000cwb.googlegroups.com...
Jody Goldberg
A small bug in gnumeric that I just patched.
I had broken the handling of array results in a non array-formula
context. The one line fix will be in the next (1.4.2) release.
Thanks
Jody Goldberg
>
> Note also that Gnumeric supports conditional counting and summing using
> SUMPRODUCT without needing to enter such formulas as array formulas. As
> an added bonus (DON'T DO THIS IF YOU NEED TO USE WORKBOOKS IN BOTH
> GNUMERIC AND EXCEL!) Gnumeric automatically converts TRUE/FALSE to 1/0
> in SUMPRODUCT, so the Excel formula
>=SUMPRODUCT(--(A1:A10>5),B1:B10)
> could be replaced with the more obvious (well, to me)
>=sumproduct(A1:A10>5,B1:B10)
> in Gnumeric. My only complaint about this is that if I enter
That is incredibly obscure. I'd guess it's specific to the
implementation of sumproduct, but I've got no idea why they'd want
that behavior. I'll have to add a note to the docs. Do you have
any details on other areas this happens ?
>=sumproduct((A1:A10>5),B1:B10)
> Gnumeric replaces this with
>=sumproduct(A1:A10>5,B1:B10)
>
> eating the unnecessary parentheses around the conditional expression.
> I'd prefer Gnumeric retained such parentheses. They may not be
> redundant in terms of formula parsing, but for some like me they make
> it clearer to distinguish the conditional terms from the values.
There have been complaints from some quarters for years on this.
None of us are incensed enough to change it. We'll likely need to
support some of this as we add 'Natural language' support.
hrl...@aol.com
>>=SUMPRODUCT(--(A1:A10>5),B1:B10)
>> could be replaced with the more obvious (well, to me)
>>=sumproduct(A1:A10>5,B1:B10)
>> in Gnumeric. My only complaint about this is that if I enter
>
>That is incredibly obscure. I'd guess it's specific to the
>implementation of sumproduct, but I've got no idea why they'd want
>that behavior. I'll have to add a note to the docs. Do you have
>any details on other areas this happens ?
I want to be sure I'm expressing this precisely.
Excel users have been using SUMPRODUCT for conditional counting and
summing for years. A brief perusal of the newsgroups
microsoft.public.excel.misc
microsoft.public.excel.newusers
microsoft.public.excel.worksheet.functions
would show that there are several questions about multiple criteria
conditional counting and summing every day, and the standard
work-around is using SUMPRODUCT. So presumably it's not the use of
SUMPRODUCT for this sort of thing that's obscure.
If you mean it's increadibly obscure that Excel doesn't convert boolean
True/False into numeric 1/0, respectively, when used in obvious numeric
contexts, that's also frequently mentioned in the Excel newsgroups
above. It's obscure for the OPs who ask for explanation of such
formulas, but it's common knowledge among advanced Excel users.
If you mean the use of -- in Excel to convert True/False to 1/0, this
rests on Excel's semantics that boolean values are converted to numeric
only when they're operands of arithmetic operators. A few long and
boring threads have established that leading, unary -- is the most
efficient and least syntactically precarious means for boolean to
numeric conversion.
As for why Microsoft might want such behavior in Excel's SUMPRODUCT, it
makes it consistent with SUM. For instance, in Excel,
=SUM({1;TRUE;FALSE;0;2}) and =SUMPRODUCT({1;TRUE;FALSE;0;2})
both return 3, but in Gnumeric the first formula returns 3 but the
second returns 4. For as long as Excel has included a SUMPRODUCT
function, it's returned exactly the same result as SUM when both were
passed the same single argument. That's not the case with Gnumeric.
If you want Gnumeric to be as compatible with Excel as possible,
Gnumeric's SUMPRODUCT is broken with respect to handling boolean TRUE
values.
>>=sumproduct((A1:A10>5),B1:B10)
>> Gnumeric replaces this with
>>=sumproduct(A1:A10>5,B1:B10)
...
>There have been complaints from some quarters for years on this.
>None of us are incensed enough to change it. We'll likely need to
>support some of this as we add 'Natural language' support.
I can understand not caring much about this when the bulk of your time
is spent writing code to implement Gnumeric rather than writing
formulas in Gnumeric to implement financial models. Redundant
parentheses make formula debugging easier, and some formulas easier to
read.
Jody Goldberg
> Jody Goldberg wrote...
>>On 2005-01-04, hrl...@aol.com <hrl...@aol.com> wrote:
> ...
>>>=SUMPRODUCT(--(A1:A10>5),B1:B10)
>>> could be replaced with the more obvious (well, to me)
>>>=sumproduct(A1:A10>5,B1:B10)
>>> in Gnumeric. My only complaint about this is that if I enter
>>
>>That is incredibly obscure. I'd guess it's specific to the
>>implementation of sumproduct, but I've got no idea why they'd want
>>that behavior. I'll have to add a note to the docs. Do you have
>>any details on other areas this happens ?
>
> True/False into numeric 1/0, respectively, when used in obvious numeric
> contexts, that's also frequently mentioned in the Excel newsgroups
> above. It's obscure for the OPs who ask for explanation of such
> formulas, but it's common knowledge among advanced Excel users.
This is what I was refering to.
That behavior was new to me.
> As for why Microsoft might want such behavior in Excel's SUMPRODUCT, it
> makes it consistent with SUM. For instance, in Excel,
>=SUM({1;TRUE;FALSE;0;2}) and =SUMPRODUCT({1;TRUE;FALSE;0;2})
>
> both return 3, but in Gnumeric the first formula returns 3 but the
> second returns 4. For as long as Excel has included a SUMPRODUCT
> function, it's returned exactly the same result as SUM when both were
> passed the same single argument. That's not the case with Gnumeric.
>
> If you want Gnumeric to be as compatible with Excel as possible,
> Gnumeric's SUMPRODUCT is broken with respect to handling boolean TRUE
> values.
Good point. Gnumeric's implementation of SUM was dutifully zering
bools and strings
return float_range_function (nodes, ei,
range_sum,
COLLECT_IGNORE_STRINGS |
COLLECT_IGNORE_BOOLS |
COLLECT_IGNORE_BLANKS,
GNM_ERROR_VALUE);
Our implementation of SUMPRODUCT was not. It used the stock
value_get_as_float
which accepted bools and strings (eg "1"). Microsoft's choice
to ignore them in SUM seems questionable to me, but gnumeric needs
to be compat with such a basic function, and there doesn't seem to
be a major benefit to making SUMPRODUCT incompatible. I've patched
it for 1.4.2 due later this week.
As a side effect of this test I noticed that gnumeric's xls export
of booleans was producing ints, rather than bools. That too has
been fixed for 1.4.2.
Thanks for the testing. It's a big help.
mwel...@gmail.com
> returned exactly the same result as SUM when both were passed the
> same single argument.
Not quite.
A1: ="1"
A2: =sumproduct(A1) --> #VALUE!
B2: =sum(A1) --> 0
A3: =sumproduct("1") --> #VALUE!
B3: =sum("1") --> 1
A4: =sumproduct({"1"}) --> 0
B4: =sum({"1"}) --> 0
A2 vs B2 and A3 vs B3 show that SUM and SUMPRODUCT do
not always yield the same result on a single argument.
B2 vs B3 vs B4 shows that Excel is just plain sick. You cannot even
take a simple scalar subexpression out and put it in its own
subexpression without changing the final result. Sick.
Morten
hrl...@aol.com
>>For as long as Excel has included a SUMPRODUCT function, it's
>>returned exactly the same result as SUM when both were passed the
>>same single argument.
>
>Not quite.
OK, when the same arguments, each of which must be syntactically valid
for SUMPRODUCT (so arrays or multiple cell, single area ranges), are
passed to SUM and SUMPRODUCT. Since online help for SUMPRODUCT is
explicit that it requires arrays as arguments, the odd case is
SUMPRODUCT accepting constant numeric scalars and references to single
cells that evaluate to numbers.
I suppose I shouldn't have been so broad in my statement, but the real
problem - and it *IS* a problem in terms of consistency with Excel -
was Gnumeric's handling of TRUE values as numeric 1s in entries in
SUMPRODUCT arguments.
> B2: =sum(A1) --> 0
...
> B3: =sum("1") --> 1
...
>B2 vs B3 vs B4 shows that Excel is just plain sick. You cannot even
>take a simple scalar subexpression out and put it in its own
>subexpression without changing the final result. Sick.
RTFM. It's well documented. From Excel's online help:
"Syntax
SUM(number1,number2, ...)
Number1, number2, ... are 1 to 30 arguments for which you want the
total value or sum.
· Numbers, logical values, and text representations of numbers that
you type directly into the list of arguments are counted. See the first
and second examples following.
· If an argument is an array or reference, only numbers in that array
or reference are counted. Empty cells, logical values, text, or error
values in the array or reference are ignored. See the third example
following.
· Arguments that are error values or text that cannot be translated
into numbers cause errors."
It's annoying, but Excel treats text constants (literal strings)
differently than it does identical text in ranges or arrays. It's due
to Excel's automatic string-number conversion semantics which are
similar to awk's. If you don't like it, enable transition formula
evaluation.