Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
Assuming your matrix at A1 is named array1
=INDEX(array1,ROW(),COLUMN())=INDEX(array1,COLUMN( ),ROW()) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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()) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matrix formatting
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix Problem | Excel Discussion (Misc queries) | |||
How to identify entries in a matrix also present in another list | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Formatting dates in the future | Excel Worksheet Functions | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) |