| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
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? |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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}
|
|
|
| Back to top |
|
|
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 ....  |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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. |
|
| Back to top |
|
|
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... |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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))} |
|
| Back to top |
|
|
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.. |
|
| Back to top |
|
|
|
