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

operation with matrix

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


Joined: 17 Jan 2007
Posts: 7

PostPosted: Tue Aug 28, 2007 5:11 am    Post subject: operation with matrix Reply with quote

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
Back to top
View user's profile Send private message
bobharvey
Super User
Super User


Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

PostPosted: Tue Aug 28, 2007 6:58 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Aug 28, 2007 7:27 am    Post subject: Reply with quote

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}
Back to top
View user's profile Send private message
lamba84
General User
General User


Joined: 17 Jan 2007
Posts: 7

PostPosted: Thu Aug 30, 2007 12:25 am    Post subject: Reply with quote

@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 .... Sad
Back to top
View user's profile Send private message
bobharvey
Super User
Super User


Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

PostPosted: Thu Aug 30, 2007 1:06 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Aug 30, 2007 1:15 am    Post subject: Reply with quote

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


Joined: 17 Jan 2007
Posts: 7

PostPosted: Sat Sep 01, 2007 12:09 pm    Post subject: Reply with quote

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Sep 01, 2007 1:15 pm    Post subject: Reply with quote

{=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))}
Back to top
View user's profile Send private message
lamba84
General User
General User


Joined: 17 Jan 2007
Posts: 7

PostPosted: Sun Sep 02, 2007 1:11 am    Post subject: Reply with quote

thaks Villeroy, it was what i was lookiging for..
Back to top
View user's profile Send private message
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