Unexpected results

[normpam]

If I apply conditional formatting to a group of cells to make any number bold if it is greater than 10,000 it works fine. But I find that any cells with labels in them also turned bold. I tried to evaluate this in two ways, but come up with weird results. If I put the letter "u" in four cells and sum them, the sum is zero, which I expected. But if I write the formula =if(B4>10000,"Y","N"), Excel will look at the character in B4 and give me the result of "Y"! This would indicate that the letter "U" has a value greater than 10,000, even though when I sum four of them together the value is zero?

Any ideas?

Thanks.

 

[IML]

Go back to your formula in conditional formatting and make sure excel didn't put quotes around it. This would surpress you formula and basically evaluate everything as true.

 

[Andrew Poulsom]

The letter u is greater than 10000 in the sort order (numbers first, letters last) in the same way that B is greater than A.

Try something like:

=IF(AND(ISNUMBER(B4),B4>10000),"Y","N")

 

[IML]

Good call Andrew. I was guilty again of not ready the question. Another route would be
changing
B4>10000
to
(B4+0)>10000

which would evaluate to error to the worksheet, but wouldn't kick off a true in conditional formatting.
This message was edited by IML on 2002-11-01 13:42

 

[normpam]

Thanks for the info. I'm still a little confused about one thing. If the letter "U" has a value, why does the sum of four "U"s come out to zero?

 

[Mark W.]

Letter "U" has an ASCII value which is returned by the CODE worksheet function.

=CODE("U") returns 85

The SUM worksheet function only operates on numeric data, and clearly "U" is text.

Perhaps you would benefit from a better understanding of collating (or sort) order... See the Excel Help topic for "Default sort orders".