Conditional formatting not recognising text

[bec74]

Hi

I have a spreadsheet with about 1000 rows which contains the results of a trading marketplace (bidder details, buy bids, sell offers, prices, units bought/sold etc).

I am trying to format 1 column (bid type) with lots of different colours depending on particular characteristics of the bidder and the outcomes of their bid, ie. if they were a priority member but were successful (green), priority member but unsuccessful (red), non-priority member... etc etc.

So I have set up a whole set of conditional formatting rules, all in a similar format eg: =AND($B2="TRUE",$C2="FALSE",$E2>0)

The problem is, any rules that involve recognising text ("TRUE" or "FALSE") are just not working. I've tried taking out the text references (eg. just highlight all successful bids green) and they work fine. I've also tried testing other formulas based on the same text eg. =IF($B2="TRUE",1,0) - they don't work either.

Any ideas on why this would be happening? I've tried switching the cell formatting from "General" to "Text", which didn't make any difference. There are no spaces before or after the text either.

Many thanks

 

[T. Valko]

In Excel there are special meanings/values associated with TRUE/FALSE.

If the cell entry is in uppercase and centered in the cell TRUE/FALSE is the default Boolean value.

"TRUE" or "FALSE" are TEXT values denoted by the quotes. "TRUE" and "FALSE" are not the same as TRUE and FALSE.

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
</tbody>[/TABLE]


A1 and A2 are the Boolean values. Notice how they are centered in the cells.

A3 and A4 are the TEXT values "TRUE" and "FALSE". Notice how they are aligned to the left.

So, maybe all you need to do is to remove the quotes from around the T/F in your formulas.

 

[T. Valko]

That graphic didn't display as I had hoped and I can't figure out how to edit the post.

So, here's a better visual...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

1​

[/td][td]

TRUE​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

2​

[/td][td]

FALSE​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

3​

[/td][td]

TRUE​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

4​

[/td][td]

FALSE​

[/td][/tr]
[tr][td="bgcolor: #c0c0c0"]

5​

[/td][td]

----------------​

[/td][/tr]
[/table]

The Boolean values are centered (by default) in the cells.

The TEXT values are aligned to the left (by default) in the cells.

 

[bec74]

Oh my goodness - that works! TRUE and FALSE weren't centered in the cell, but removing the quotes still fixed the problem. Thank you so much - all that time I spent googling for an solution and you fixed it within a few minutes!!!

 

[T. Valko]

Good deal. Thanks for the feedback!