[Linux-disciples] Re: Array formulas in Gnumeric

[Stephen R Laniel]

On Wed, Sep 14, 2005 at 11:21:36AM -0400, Stephen R Laniel wrote:
> 2  1.50  0     =sum(if(and($A$2:$A$30000>=B2,$A$2:$A$30000<B3),1,0))

Someone on the Gnumeric list reminds me of a bit of Excel
syntax that also works in Gnumeric, which I had forgotten
about. A better way to do what I want above is

=sum(($a$2:$a$30000>=b2)*($a$a:$a$30000<b3))

which relies on the trick that the two inner blocks of
parentheses are predicates, meaning they return 0 or 1
according to whether the conditions within them are false.
And Gnumeric recognizes that they apply to *blocks* of cells
rather than to individual cells, so the sum behaves
appropriately. Nice.

-- 
Stephen R. Laniel
steve at laniels.org
+(617) 308-5571
http://laniels.org/
PGP key: http://laniels.org/slaniel.key
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://lists.bostoncoop.net/pipermail/linux-disciples/attachments/20050914/5414d289/attachment.pgp