Conditional Formating Oddity

[MikeG]

I am getting a strange thing with Conditional Formating (CF) in Excel 2007, which I wonder if others get, and if anyone has a logical explantion for.

I set up a simple CF rule that says if a given Cell is >3 then color the cell red. And sure enough if I enter a 2, the cell stays uncolored, but if I enter 4 it goes red.

But here's the strange thing, if I enter any text value(s) into the cell, for example, the letter "a", the cell goes red.

Do others get the same thing? Is there a reason for this?

Thanks,

MikeG

 

[Jonmo1]

Yep, you have to use "Use Formula..."

And use

=AND(ISNUMBER(A1),A1>3)

 

[T. Valko]

I am getting a strange thing with Conditional Formating (CF) in Excel 2007, which I wonder if others get, and if anyone has a logical explantion for.

I set up a simple CF rule that says if a given Cell is >3 then color the cell red. And sure enough if I enter a 2, the cell stays uncolored, but if I enter 4 it goes red.

But here's the strange thing, if I enter any text value(s) into the cell, for example, the letter "a", the cell goes red.

Do others get the same thing? Is there a reason for this?

Thanks,

MikeG

In Excel any text value has a higher value than any numeric value.

So, logically speaking:

"a">3 = TRUE

Try this formula on the worksheet:

="a">3

Or if you reverse the logic:

=3<"a" = TRUE

To account for that in CF test that the cell actually contains a number:

=AND(COUNT(A1),A1>3)

 

[MikeG]

Yep, you have to use "Use Formula..."

And use

=AND(ISNUMBER(A1),A1>3)

Thanks!

Any guess as to why Microsoft did it this way?

MikeG

 

[Jonmo1]

I think it has to do with the way data is sorted...

If you have a column of Data (mixed numbers and text), and you sort it ASCENDING, Numbers will come first.

 

[MikeG]

Thanks T. V.

 

[T. Valko]

Thanks T. V.

You're welcome!