Formulas returning blank being read as >0 Why?

[Bonkers Bill]

Hello, i am using the following formula in excel 2007

=IF($O$3="HOME",IF(INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0))="","",IF(INDEX(AC5:DV5,MATCH($R$4,$AC$3:$DV$3,0))="L","",INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0)))),"")

when this formula returns "" blank and i copy and use paste special, values only, another formula is reading the "" blanks as >0, Why? and what can i do to stop this?

 

[Andrew Poulsom]

Null is greater than zero in the sort order (as is any text).

 

[T. Valko]

Hello, i am using the following formula in excel 2007

=IF($O$3="HOME",IF(INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0))="","",IF(INDEX(AC5:DV5,MATCH($R$4,$AC$3:$DV$3,0))="L","",INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0)))),"")

when this formula returns "" blank and i copy and use paste special, values only, another formula is reading the "" blanks as >0, Why? and what can i do to stop this?

The reason is because in Excel TEXT has a higher value than any number.

="X">1000000 = TRUE

What to do about it depends on how you're using the result of a formula that returns a blank.

We'd need to know what your other formula is doing to make a specific suggestion.

 

[Bonkers Bill]

This is the formula that finds blank cells as being >0 and returns W
=IF(Y5=0,"W",IF(S5>0,"W",IF(S6>0,"L",IF(ISNA(INDEX(Z$1:Z$1298,MATCH(1,IF(S$1:S$1297=N5,IF(U$1:U$1297=O5,1)),0))),"",INDEX(Z$1:Z$1298,MATCH(1,IF(S$1:S$1297=N5,IF(U$1:U$1297=O5,1)),0))))))

 

[T. Valko]

This is the formula that finds blank cells as being >0 and returns W
=IF(Y5=0,"W",IF(S5>0,"W",IF(S6>0,"L",IF(ISNA(INDEX(Z$1:Z$1298,MATCH(1,IF(S$1:S$1297=N5,IF(U$1:U$1297=O5,1)),0))),"",INDEX(Z$1:Z$1298,MATCH(1,IF(S$1:S$1297=N5,IF(U$1:U$1297=O5,1)),0))))))

One way...
IF(AND(N(S5),S5>0),"W",...

 

[Bonkers Bill]

What does the N stand for?

 

[T. Valko]

What does the N stand for?

N stands for number.

If the cell contain a number then N(...) returns that number.

If the cell contains text then N(...) returns 0.

 

[T. Valko]

One way...
IF(AND(N(S5),S5>0),"W",...

Now that I think about it, we can reduce that to:

IF(N(S5)>0,"W",...

 

[Bonkers Bill]

The 1st way using the N() works but i am still having other problems in other formulas not as easy to change as this one, is there a way to sort the problem with the original formula where i copy and then special paste, this formuls is

=IF($O$3="HOME",IF(INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0))="","",IF(INDEX(AC5:DV5,MATCH($R$4,$AC$3:$DV$3,0))="L","",INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0)))),"")

 

[T. Valko]

The 1st way using the N() works but i am still having other problems in other formulas not as easy to change as this one, is there a way to sort the problem with the original formula where i copy and then special paste, this formuls is

=IF($O$3="HOME",IF(INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0))="","",IF(INDEX(AC5:DV5,MATCH($R$4,$AC$3:$DV$3,0))="L","",INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0)))),"")

I'm not sure what you're asking.

If that formula results in blank "", then what?

Are you referencing the result of that formula and it's causing some problem?