[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
lamba84
General User

Joined: 17 Jan 2007
Posts: 7

 Posted: Tue Aug 28, 2007 5:11 am    Post subject: operation with matrix hi everybody, does anyone know a way to confront 2 matrix in calc and give back true if one value is in both? thanks a lot
bobharvey
Super User

Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

 Posted: Tue Aug 28, 2007 6:58 am    Post subject: I have no knowledge of matrix functions (the {} brigade). No doubt someone cleverer than I will come along soon. meanwhile have a look at http://virtualvault.freedom2surf.net/F/18870799/EMBED It has two 3x3 matricies, a 3x3 set of comparisons, and a result. The comparisons could, of course, be on another sheet. Is that what you meant?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10104
Location: Germany

Posted: Tue Aug 28, 2007 7:27 am    Post subject:

Try array function
 Code: {=COUNTIF(\$Sheet1.\$A\$1:\$D\$4;\$Sheet2.\$A\$1:\$D\$4)}

Shows the count of cells in first range having an equivalent in second range. Treats blanks as zero.

Same with TRUE/FALSE if count >0
 Code: {=COUNTIF(\$Sheet1.\$A\$1:\$D\$4;\$Sheet2.\$A\$1:\$D\$4)>0}
lamba84
General User

Joined: 17 Jan 2007
Posts: 7

 Posted: Thu Aug 30, 2007 12:25 am    Post subject: @bobharvey: thaks for the replay, but i'm looking for something that compare all the cell of the first matrix with all the cell of the other matrix @Villeroy: the function countif seems to be the right one for me but it give me back a #value error ....
bobharvey
Super User

Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

Posted: Thu Aug 30, 2007 1:06 am    Post subject:

 lamba84 wrote: the function countif seems to be the right one for me but it give me back a #value error

No, it's those curly bracket chaps again. I just found the help for "Entering Matrix Formulas" and it tells you to enter the formula without the {} and press shift-ctrl-enter to finish the editing.

I got back a result array the same size as the input ones, with counts based on hunting for the elements of the second array to the first.

So now I've learned a lot about matrix forumulae.

I've uploaded a new version of scrap.ods 'cos it was fun.
http://virtualvault.freedom2surf.net/F/19208769
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10104
Location: Germany

Posted: Thu Aug 30, 2007 1:15 am    Post subject:

The formula returns #VALUE if
- it was not entered as array formula. Put in into a cell and finish with Ctrl+Shift+Enter
- you are using a rather old version of OOo (before 2.0)
- there is at least one #VALUE error in one of the referenced ranges

Without array context:
 Code: =COUNTIF(\$Sheet1.\$A\$1:\$D\$4;\$Sheet2.A1)

This gets a single criterion from relative reference A1. Copy this cell down and to the right.
lamba84
General User

Joined: 17 Jan 2007
Posts: 7

 Posted: Sat Sep 01, 2007 12:09 pm    Post subject: solved the problem with the #value error, was a missing " in the code for strings. using countif I can only say if an element of the first matrix has the same value of the one in the second matrix, but i'd like to find if any of the value of the first matrix is in the second...
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10104
Location: Germany

 Posted: Sat Sep 01, 2007 1:15 pm    Post subject: {=SUM(COUNTIF(\$Sheet1.\$A\$1:\$D\$4;\$Sheet2.\$A\$1:\$D\$4)>0)} returns zero if no value of the first matrix is in the second. Take care of blank cells, which are equivalent to zero. Test with {=SUM(ISBLANK(A1:D4))}
lamba84
General User

Joined: 17 Jan 2007
Posts: 7

 Posted: Sun Sep 02, 2007 1:11 am    Post subject: thaks Villeroy, it was what i was lookiging for..
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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