Why does blank text equal > 0?

[robotman]

I have a spreadsheet where in some cells I want the cell to show
nothing if a condition is not met:

For example, in cell A1:
= IF (a = b, a + b, "")

If in A2 when a<>b, I check to see if this value is greater than 0, it
returns TRUE:
= A1 > 0

I can even simplify A1 just to be:
= ""

and the it still returns that this cell >0.

Can someone please explain and maybe offer an alternative way to
display nothing in a cell that will register as a 0?

Thanks.

John

 

[Biff]

TEXT will always evaluate to be greater than ANY number. What you can do is
test the blank/empty cell to see if it actually contains a number:

=AND(ISNUMBER(A1),A1>0)

Biff

 

[robotman]

The only catch is that this is all part of a complicated SUM array to
count if two conditions are met:

={SUM(IF('Raw Data'!$D$3:$D$500="502",IF('Raw
Data'!$P$3:$P$500>180,1,0),0))}

So in this case, if a cell in Col D = 502 AND a cell in Col P > 180 it
would add 1.

Any suggestions on how to do this if I have blank cells (i.e. "") in
column P if there is no number? I don't really want to add another IF
statement to the array.

Thanks...

 

[Biff]

Try this (normally entered, not an array formula):

=SUMPRODUCT(--('Raw Data'!$D$3:$D$500="502"),--(ISNUMBER('Raw
Data'!$P$3:$P$500)),--('Raw Data'!$P$3:$P$500>180))

Are you sure you want this:

'Raw Data'!$D$3:$D$500="502"

Enclosing a number in quotes makes it a TEXT value and not a NUMERIC value.
Try removing the quotes from around 502.

Biff

 

[robotman]

Yes, the "502" is actually a text field.

SUMPRODUCT seems like an easy way to handle multiple conditions with
out getting into arrays.

What does the "--" mean? Does that convert TRUE from -1 to 1? Can
"--" used in other formulas / VBA?

Thanks!

 

[Biff]

SUMPRODUCT seems like an easy way to handle multiple conditions with

out getting into arrays.

Yes, it is. Sumproduct is extemely useful and versatile.

What does the "--" mean? Does that convert TRUE from -1 to 1?

Yes, but it's a 2 step process. -TRUE = -1 then --1 = 1. Basically, it
converts TRUE to 1 and FALSE to 0.

Can "--" used in other formulas / VBA?

I'm not sure about VBA but it can be used in any formula where you need/want
to convert TEXT numbers/logicals into NUMERIC values.

See this for a detailed explanation:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Biff

 

[Guest]

Looks like it works in VBA, but I've never used it in a practical application
so I don't know what limitations or pitfalls may exist. In VBA, however,
TRUE = -1, which may be what prompted the OP's question:

Sub test()
x = --IsNumeric(5)
MsgBox x
End Sub