Matrix formatting

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

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

And give it a nice pattern.

Corey wrote:
>
> 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

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" wrote:

> Select your range (say A1:D4)
> and with A1 the activecell
> format|conditional formatting
> formula is:
> =COUNTIF($A$1:$D$4,A1)>1
>
> And give it a nice pattern.
>
> Corey wrote:
> >
> > 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
>

The 2 in the third column matched the 2 elsewhere.

I guess I didn't understand the question.

Corey wrote:
>
> 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" wrote:
>
> > Select your range (say A1:D4)
> > and with A1 the activecell
> > format|conditional formatting
> > formula is:
> > =COUNTIF($A$1:$D$4,A1)>1
> >
> > And give it a nice pattern.
> >
> > Corey wrote:
> > >
> > > 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
> >

--

Dave Peterson

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" wrote:

> The 2 in the third column matched the 2 elsewhere.
>
> I guess I didn't understand the question.
>
> Corey wrote:
> >
> > 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" wrote:
> >
> > > Select your range (say A1:D4)
> > > and with A1 the activecell
> > > format|conditional formatting
> > > formula is:
> > > =COUNTIF($A$1:$D$4,A1)>1
> > >
> > > And give it a nice pattern.
> > >
> > > Corey wrote:
> > > >
> > > > 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
> > >
>
> --
>
> 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.

Corey wrote:
>
> 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" wrote:
>
> > The 2 in the third column matched the 2 elsewhere.
> >
> > I guess I didn't understand the question.
> >
> > Corey wrote:
> > >
> > > 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" wrote:
> > >
> > > > Select your range (say A1:D4)
> > > > and with A1 the activecell
> > > > format|conditional formatting
> > > > formula is:
> > > > =COUNTIF($A$1:$D$4,A1)>1
> > > >
> > > > And give it a nice pattern.
> > > >
> > > > Corey wrote:
> > > > >
> > > > > 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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

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" wrote:

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

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)

Thanks!!! This worked great!!

"Herbert Seidenberg" wrote:

> 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)
>
>