Why does blank text equal 0?
 

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

Why does blank text equal 0?
 

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),A10)

Biff

wrote in message
ups.com...
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

Why does blank text equal 0?
 

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$500180,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...

Why does blank text equal 0?
 

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$500180))

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

wrote in message
oups.com...
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$500180,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...

Why does blank text equal 0?
 

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!

Why does blank text equal 0?
 

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

wrote in message
oups.com...
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!

Why does blank text equal 0?
 

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:

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

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


"Biff" wrote:

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

wrote in message
oups.com...
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!