Re: RFC - Using Gnumeric as a Database source

["Hal Ashburner" <h ashburner iname com>]

Hi Uri David,
There was some talk vaguely related to this.
http://bugzilla.gnome.org/show_bug.cgi?id=94298

When implicit iteration of operators is supported you could do somthing like this:

=sum(if(A1:A10="value1",if(B1:B10="value2",C1:C10,0),0))
and enter with Ctrl+Shift+Enter so that is works as an array function
and looks something like
{=sum(if(A1:A10="value1",if(B1:B10="value2",C1:C10,0),0))}

(note you do not enter the { and } with the keyboard, but edit the function and hit Ctrl + shift + enter.)
 
It's a technique I use a lot on a proprietary product that I use for work (someone else's decision), for 
precisely the reasons you mention. It's quick, the person coming next has some chance of being able to 
maintain, especially if you leave them a note in the sheet similar to the above.

=SUMIF(A15:F63, (B="Uri David" AND C="Muscle), G15:G63)
becomes
=sum(if(B16:B63="Uri David",if(C16:C63="Muscle",G15:G63,0),0))
entered with Ctrl+Shift+Enter
And you can go further than two criteria, the limit is the number of nested functions supported, from memory. 
But if you get more than a few criteria, it's probably worthwhile going for a database. In the proprietary 
version, you're limited to about 4k rows, last I checked.


Hal
(Feeling very sheepish he's not ready to hack implicit iteration operators himself - yet. The itch is there 
but I can't quite reach...)

----- Original Message -----
From: Uri David Akavia <uridavid netvision net il>
Date: Sun, 21 Dec 2003 17:44:46 +0200
To: Gnumeric Mailing list <gnumeric-list gnome org>
Subject: RFC - Using Gnumeric as a Database source

> Hello all.
>
> I'm sending the mailing list a feature I would like to see implemented
> in the 1.3 development cycle - Using Gnumeric as a databse source.
>
> I'll explain: I've used Excel as a small database (we're talking several
> hundred lines, where each collumn is a distinct value). This is simpler
> than using Acess (Blah) and it is generally simple enough so people can
> maintain it, even after I'm gone.
> This is for tissue harvested during research. The general idea is that
> each line has Collecter, Tissue type, Date, Amount and such like.
> I use SUMIF and COUNTIF to measure the amount of Muscle (for example)
> that I collected. However, both SUMIF and COUNTIF let you only specifiy
> one criteria, so to do something like this I have to use sorting and
> other yucks.
> I would like expanded SUMIF and COUNTIF - for example
> SUMIF(A15:F63, (B="Uri David" AND C="Muscle), G15:G63).
>
> However, it got me thinking. Writing something like this in SQL would be
> incredibly easy, but it means that I have to set up Gnumeric as its own
> Database source. I think that such enhancment would allow me to set up
> much more complex questions, and change them on the fly.
> How difficult would it be?
>
> I'm open to comments, and I hope that it isn't too difficult to
> implement.
>
> Yours,
>
> Uri David Akavia
>
> _______________________________________________
> gnumeric-list mailing list
> gnumeric-list gnome org
> http://mail.gnome.org/mailman/listinfo/gnumeric-list

-- 
___________________________________________________________
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm