OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Array/Matrix functions

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
marafaka
Guest





PostPosted: Tue Apr 08, 2003 4:02 am    Post subject: Array/Matrix functions Reply with quote

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?
Back to top
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Tue Apr 08, 2003 11:10 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
marafaka
Guest





PostPosted: Wed Apr 09, 2003 9:33 pm    Post subject: Reply with quote

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(...))}.
Back to top
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Wed Apr 09, 2003 9:47 pm    Post subject: Reply with quote

No problem, look at the database functions. DMAX is the one that you want ... the help is quite explicit.
Back to top
View user's profile Send private message
marafaka
Guest





PostPosted: Fri Apr 11, 2003 3:37 am    Post subject: Reply with quote

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.
Back to top
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Fri Apr 11, 2003 12:19 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
marafaka
Guest





PostPosted: Tue Apr 15, 2003 5:53 am    Post subject: Reply with quote

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.
Back to top
Guest






PostPosted: Tue Apr 15, 2003 12:51 pm    Post subject: Reply with quote

No. I think that this is strictly QBE
Back to top
Guest






PostPosted: Tue Apr 15, 2003 9:13 pm    Post subject: Reply with quote

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.
Back to top
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group