Conditional formatting TRUE/FALSE

[Llupo01]

Hello everyone,

I have quick question. If conditional formatting uses TRUE/FALSE logic (is applied only on TRUE values), and Excel takes 0 as FALSE and 1 as TRUE...what is explanation for =MOD(ROW(),3) where values are always 1,2,0...and in this case, CF will also be applied on 2...

if we use =MOD(ROW(),4), CF will format values 1,2,3...can somebody pls give me quick answer why also values 2,3,4 etc are formatted?

Thank you very much in advance.

Tomas

 

[footoo]

You need to write the formula so that it returns True or False. For example :

=MOD(ROW(),3)=2

=MOD(ROW(),4)=0

 

[Llupo01]

Thank you, but I dont need to use this formula...I need to use my formulas as posted before, because they are working just fine...

My question is, when 0 = FALSE, 1 = TRUE, why also fields, where MOD function returns 2,3 etc...is formatted??

 

[Peter_SSs]

The answer is that the conditional formatting treats 0 as false and any other number (eg. 3, -0.4, 26, 5000) as True.

Not only CF formulas, but other formulas too. Try this in a blank cell
=IF(-8.6,"the test was true", "the test was false")

 

[Llupo01]

Thank you Peter, thats what I wanted to verify...This answer is actually helping me with different formula, I just wanted to be sure about TRUE/FALSE logic. I thought, that 0 if FALSE and every other number is TRUE, but I wasnt sure

Thank you again.

Tom

 

[footoo]

What formula are you putting in CF?

If you are not restricting it to returning either True or False, CF will treat anything returned other than 0 as True.

and for your information, =mod(row(),3) <>2, but it depends on which row its used in...so =MOD(ROW(),3) will be always 0,1,2,3,0,1,2,3,0,1,2,3.

That's why you need to specify in the formula what remainder you want. (Thanks for the information)
(Note : =MOD(ROW(),3) will return 0, 1, or 2 - not 0, 1, 2, or 3)

 

[Peter_SSs]

Thank you Peter,

No problem - glad to help.