Array/Matrix functions

marafaka · Guest

Hi, I'm trying to get some conditional data out of my tables, but cannot force IF() function to return an array in a formula like {=IF((B7:B13)>2;1;0)}. Am I doing something wrong?

dfrench · Posted: Tue Apr 08, 2003 11:10 am Post subject:

Wrong? Well that may be a little harsh but I do not think that the 'Test' part of the condition is valid. The IF function does not support an array here you will need to use a function that evaluates to a single value or an array of IF functions.

marafaka · Guest

Then how would you pick up the largest date in a column A, but only from those rows, that have certain string in column B? There's SUMIF (i.e. {=SUMIF(A7:A13;"aha";B7:B13)}), but no MAXIF, so my idea was to filter an array with {=MIN(IF(...))}.

dfrench · Posted: Wed Apr 09, 2003 9:47 pm Post subject:

No problem, look at the database functions. DMAX is the one that you want ... the help is quite explicit.

marafaka · Guest

Thanks man, I was checking database functions the other day and they do what I want, but do I need to put queries on the page or I can somehow write them into the formula, i.e.:

=DMIN(B6:E13;"foo";"bar">100)

Regards.

dfrench · Posted: Fri Apr 11, 2003 12:19 pm Post subject:

Difficult to explain without pictures!
The functions use the form of Query By Example
So the query part of the formula (search criteria) must be on the sheet in the form of an array with the same column headings as the source data. Any or All the source columns can take part in the selection of rows to be operated on. Rows of the search criteria are treated in "OR" relationship.
Database>>>

Col1 Col2 Bar Col4
1 Foo 10 10
2 Foo 20 20
3 Foo 30 30
4 Notfoo 150 150
5 notfoo 160 160
6 notfoo 170 170
7 foo 180 180
Search Criteria (I6:J7)>>>
Col2 Bar
Foo >100
Formula>>>
=DMIN(B6:E13;"Col4";I6:J7)
This reads as select rows where Col2=Foo AND Bar>100 and give the minimum of Col4.

Changing the data in the search criteria changes the query.
I suggest that you copy the example from the HELP text for Database functions which works for me.

marafaka · Guest

Thanks man. I had a hard time figuring it out form the Help, but I did it. Now I'd only like to know if it is posible to write that search criteria in a formula, not on the page? In your example that would be sth like:

=DMIN(B6:E13;"Col4";Col2="Foo" AND Bar>100)

Thanks.

Guest · Posted: Tue Apr 15, 2003 12:51 pm Post subject:

No. I think that this is strictly QBE

Guest · Posted: Tue Apr 15, 2003 9:13 pm Post subject:

OK, I'll try to adapt. I just migrated from ms and some array formulas (i.e. {=MIN(IF(H4:H50=G8;C4:C50)))} are not working. Otherwise I like OO very much.