Matrix formatting

[Guest]

I have a matrix similar to below. I'd like for those amounts that agree to be
shaded and those that do not, to not be shaded. For example, everything in
the table would be shaded except for b:c / c:b. Any suggestions?

X A B C
A 0 2 1
B 2 0 2
C 1 3 0

 

[Dave Peterson]

Select your range (say A14)
and with A1 the activecell
format|conditional formatting
formula is:
=COUNTIF($A$1:$D$4,A1)>1

And give it a nice pattern.

 

[Guest]

This kind of works for the most part. It seems to search for any match, not
the ones at the corresponding vector points. For instance, the formula also
highlights the 2 in the third column. ??

 

[Dave Peterson]

The 2 in the third column matched the 2 elsewhere.

I guess I didn't understand the question.

 

[Guest]

The matrix reads as a relationship to corresponding criteria.

Does row C, column B = column B, row C (inverse of each other)

Only two intersections are being compared here. I'm thinking it might have
to be done manually.

 

[Dave Peterson]

Maybe you could use =index() and invert the columns and rows.

But I think you'd have to specify more info--well at least for me to attempt it.

The address the table is located and a few examples of which cells to check.

The matrix reads as a relationship to corresponding criteria.

Does row C, column B = column B, row C (inverse of each other)

Only two intersections are being compared here. I'm thinking it might have
to be done manually.

 

[Herbert Seidenberg]

Assuming your matrix at A1 is named array1
=INDEX(array1,ROW(),COLUMN())=INDEX(array1,COLUMN(),ROW())

 

[Guest]

I'm not sure I understand. I went ahead and name A1 to 'array1'. I then put
your formula in the conditional formatting step. Nothing happened. ??

 

[Herbert Seidenberg]

Move the matrix so A1 is the upper left cell of the matrix.
Then select all the cells in the matrix and name the matrix array1.
Use Insert > Name > Define
or don't use a name and say $A$1:$D$4 instead.
If your matrix is located anyplace else on the spreadsheet, use
=INDEX(array1,ROW()-ROW(array1)+1,COLUMN()-COLUMN(array1)+1)=
INDEX(array1,COLUMN()-COLUMN(array1)+1,ROW()-ROW(array1)+1)

 

[Guest]

Thanks!!! This worked great!!