Blank cell appearing as greater than 1000?

[davidx]

Good evening, for some reason my formula in cell d3 is saying that the blank cell c3 (containing "") is actually containing a value greater than 1000.

Frustration has begin to devour me, any ideas on this one folks, thanks very much, file attached

[Pete_UK]

Try it like this:

=IF(AND(ISNUMBER(C3),C3>1000),1,0)

Hope this helps.

Pete

[Ace_XL]

Excel does read blank cells as greater than numbers since blank is CHAR(1) and number s start at CHAR(48)

In C3 change formula to

=IF(ISERROR(B3*A3),0,B3*A3)

format cells as General;; if you do not want zero values displayed

[Pepe Le Mokko]

The "" you introduced is considered by XL as a null string, and therefore is text. ( enter = istext(c3) somewhere to confirm)
As XL evaluates a cell type ( =type() ) before comparing values and that =type(text) returns 2 where type(number) returns 1, you will always see that text is considered larger than any number.

Don't ask me why, I don't know the reason behind this.

[tigeravatar]

The reason is for sorting purposes. When sorting ascending, numbers will always be before text. This is consistent with sorting standards.

From the National Information Standards Organization:
http://www.niso.org/publications/tr/tr03.pdf

On numbers (section 3.5):

3.5 Numerals (0 through 9)

All headings beginning with a numeral should be arranged ahead of any heading beginning
with a letter, not as if spelled out (Figure 1). See also Section 6.