How to find a third true-false relationship between three groups ofvariables knowing two

[Edu]

Hi guys,

Here's a question that while straightforward I am stuck with in terms
of carrying it out in Excel:

I have three groups of values - say Letters (A, B, C), Numbers
(1,2,3,4) and Variables (x,y,z). I have two true-false relationships
between two pairs - say Letters-Numbers and Letters-Variables and I
would like to know the remaining relationship i.e. Variables-Numbers.

- Say Letters-Numbers is thus (where the value of a cell can either be
1-true or 0-false):

1 2 3 4
A 1 0 0 1
B 0 0 0 1
C 0 1 1 0

- And Letters-Variables is thus:

x y z
A 0 1 0
B 1 0 0
C 0 0 1

- I am trying to find the combination Variables-Numbers thus (Where
the logic is: If "1" in "A" is TRUE and "A" in "y" is TRUE then "y" in
"1" is TRUE):

1 2 3 4
x 0 0 0 1
y 1 0 0 1
z 0 1 1 0

Any help on this one would be very, very appreciated!

Cheers,
Edu

 

[David Biddulph]

If your first array is in $B$2:$E$4 (with row and column headers to the left
and above, though they're not used),
and your second array is in $B$8:$D$10 (again with row & column headers to
the left and above)

Try
=INDEX($B$2:$E$4,MATCH(1,OFFSET($B$8,0,MATCH($A14,$B$7:$D$7,0)-1,3,1),0),COLUMNS($B$14:B14))
in B14 and copy to the right and down.

 

[Ron Rosenfeld]

Hi guys,

Here's a question that while straightforward I am stuck with in terms
of carrying it out in Excel:

I have three groups of values - say Letters (A, B, C), Numbers
(1,2,3,4) and Variables (x,y,z). I have two true-false relationships
between two pairs - say Letters-Numbers and Letters-Variables and I
would like to know the remaining relationship i.e. Variables-Numbers.

- Say Letters-Numbers is thus (where the value of a cell can either be
1-true or 0-false):

1 2 3 4
A 1 0 0 1
B 0 0 0 1
C 0 1 1 0

- And Letters-Variables is thus:

x y z
A 0 1 0
B 1 0 0
C 0 0 1

- I am trying to find the combination Variables-Numbers thus (Where
the logic is: If "1" in "A" is TRUE and "A" in "y" is TRUE then "y" in
"1" is TRUE):

1 2 3 4
x 0 0 0 1
y 1 0 0 1
z 0 1 1 0

Any help on this one would be very, very appreciated!

Cheers,
Edu

=MMULT(Ltrs_Vars,Ltrs_Nums)

entered as an array-formula in a 3x4 array


--ron

 

[David Biddulph]

Ah, yes. Much simpler than my formula.

 

[Edu]

Beautifully simple answer. Thanks guys.

 

[Ron Rosenfeld]

Beautifully simple answer. Thanks guys.

You're welcome. Thanks for the feedback.
--ron