# Re: RFC - Using Gnumeric as a Database source

• From: "Hal Ashburner" <h ashburner iname com>
• To: "Uri David Akavia" <uridavid netvision net il>, "Gnumeric Mailing list" <gnumeric-list gnome org>
• Subject: Re: RFC - Using Gnumeric as a Database source
• Date: Sun, 21 Dec 2003 19:10:56 -0500

```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