Conditional Formatting

[Lorrie]

Just curious: If text has a value of 0 and I run
conditional formatting on a column (which includes a
header row of text), requesting that special formatting
be displayed in every cell that is "Greater Than" 1000.
The results include special formatting for the text
cell. That surprises me as Text has a zero value which
is certainly less then 1000. It's not a major problem
because I then just use the format painter to re-format
the header row. It just does not seem right to me. Does
any one have a comment or possibly a reason that this is
happening?

We are use Excel 2002 XP.

Thank you - Lorrie White

 

[Biff]

Hi Lorrie,

Text does not have a considered value of 0. I don't know
the exact reasoning behind it but text has an explicit
value that is greater than any numeric value no matter how
big. Try this:

In a cell,say A1,enter this numeric value : 1E+308
That is the largest numeric value that can be entered into
a cell. That's a ridiculously large number.

Now in another cell,say A2,enter any text string you want.
In cell A3 enter this formula: =A2>A1. And the result will
be TRUE.

So with that being the case, just exclude the text header
when you do your CF.

Biff