Blank cells evaluates to greater than a number

[dreid1011]

Good morning,

This is an odd one, and I am not putting it in the right words to find elsewhere.

I am working on a sheet with numerous Index/Match and Nested IFs. All of these return "" with no manual data entered. The problem is that some of the formulas are comparing the values in some cells to see if they are greater than a specific number, and while blank "", the comparison is returning TRUE.

Ex:

J5 contains some nested Index/Match's in an IF, and by default, returns "".

J4 has an IF with the comparison J5>100. This is returning TRUE while J5 is "".

=ISNUMBER(J5) returns false.

What am I missing here?

 

[Ron Coderre]

Numbers are considered "smaller" than text.
Try this example:
• Put the series 48 through 90 in cell A1:A43 on a blank sheet
• Put this formula in B1 and copy it down:
=CHAR(A1)

Number are "less than" text.

If you want your test to work properly, you might try something like this:'
=N(J5)>100

The N() function will convert the text-blank to a zero.

Is that something you can work with?

 

[dreid1011]

That should work. Thanks.