Tough conditional formatting question

[Guest]

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

 

[Guest]

Unless I'm missing something, wouldn't this work?:

Select A2
<Format><Conditional Formatting><Condition 1>
Cell Formula is: =COUNTIF(B22,0)>0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

you can't do this with conditional formatting, because you can only have up
to three conditions (four if you include the default condition).

You could insert a column inbetween columns A and B, and insert this formula
=IF(C1<>0,"X","")&IF(D1<>0,"Y","")&IF(E1<>0,"Z","")
It would look like this

Criteria XYZ X Y Z SUM
a YZ 0 1 3 4
b XZ 1 0 5 6
c XY 7 9 0 16
d Z 0 0 2 2
e Y 0 7 0 7
f X 4 0 0 4

 

[Guest]

I'd like to have a different format for each of those conditions I put down,
not the same one for each. Would this formula do that? I've never seen
something like that >0 after the parentheses before, what does that do?
Thanks!

Unless I'm missing something, wouldn't this work?:

Select A2
<Format><Conditional Formatting><Condition 1>
Cell Formula is: =COUNTIF(B22,0)>0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

 

[Guest]

No offence Ron, but this won't work. the formula counts the number of times
0 appears in the three columns, and compares that count to 0. If the count
is greater than zero, then the formula results in TRUE and triggers the
conditional format. So basically you will get the same format for all cases
except when all three columns are non-zero.

I'd like to have a different format for each of those conditions I put down,
not the same one for each. Would this formula do that? I've never seen
something like that >0 after the parentheses before, what does that do?
Thanks!

Unless I'm missing something, wouldn't this work?:

Select A2
<Format><Conditional Formatting><Condition 1>
Cell Formula is: =COUNTIF(B22,0)>0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

 

[Guest]

Thanks a lot, Sloth, that worked great!

you can't do this with conditional formatting, because you can only have up
to three conditions (four if you include the default condition).

You could insert a column inbetween columns A and B, and insert this formula
=IF(C1<>0,"X","")&IF(D1<>0,"Y","")&IF(E1<>0,"Z","")
It would look like this

Criteria XYZ X Y Z SUM
a YZ 0 1 3 4
b XZ 1 0 5 6
c XY 7 9 0 16
d Z 0 0 2 2
e Y 0 7 0 7
f X 4 0 0 4

 

[Guest]

Understood, Sloth... No offense taken. I just wanted to make sure the OP
didn't want the same CF for each case (which effectively summarized to a zero
in any of the columns) since no list of specific formats was mentioned.

***********
Regards,
Ron

XL2002, WinXP-Pro

No offence Ron, but this won't work. the formula counts the number of times
0 appears in the three columns, and compares that count to 0. If the count
is greater than zero, then the formula results in TRUE and triggers the
conditional format. So basically you will get the same format for all cases
except when all three columns are non-zero.

I'd like to have a different format for each of those conditions I put down,
not the same one for each. Would this formula do that? I've never seen
something like that >0 after the parentheses before, what does that do?
Thanks!

Unless I'm missing something, wouldn't this work?:

Select A2
<Format><Conditional Formatting><Condition 1>
Cell Formula is: =COUNTIF(B22,0)>0
Click the [Format...] button and set the format you want

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hi all,
I have a sequence of five columns, they look something like this:

Criteria X Y Z SUM
a 0 1 3 4
b 1 0 5 6
c 7 9 0 16
d 0 0 2 2
e 0 7 0 7
f 4 0 0 4

Is there a way to use conditional formatting to mark the entry in the
criteria column for these conditions:
Nonzero number only in first column
Nonzero number only in second column
Nonzero number only in third column
Nonzero number only in first and second column
Nonzero number only in first and third column
Nonzero number only in second and third column

I know this is kind of a complex question, thanks in advance!

PS, thanks so much to the person who helped me with my last question if he
reads this, I was sick for a day and I couldn't get back here, then I
couldn't find my old post to thank you!

jezzica85

 

[Guest]

Actually, there's one more question I just thought of--is there an easy way
to list the individual criteria in alphabetical order that have, say, X in
the second column?
Thanks again!

 

[Guest]

You can use a standard Data->Sort to alphabetize the Criteria column, and
then use Data->Filter->Autofilter to filter out everything but X's.