If result of formula is "" (Blank) why is this considered > a num.

[Guest]

I have a cell formula that returns either a number or "" (blank). This works
fine but when I try to compare the result with another number to see which is
greater, I'm having trouble figuring out how Excel treats the result. If the
formula returned a number (say 2.0) and the other cell contained say 3.0,
then Excel can correctly recognise that 3>2. However, if the result was "",
then it says that "">3. I don't know why this is. You would think that ""
would be treated as zero which would be less than 3, not greater than.

 

[Peo Sjoblom]

Actually it is treated as text and text is greater than any number in Excel

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

Try to substitute "" with 0
any text is greater than A number
example: A1 =me, B1 =3
formula A1>B1 (the answere is TRUE)

 

[Guest]

try max(a1,b1)
a1=3
b1 is blank

been dribled2

 

[Guest]

Thank you all for replies so far; all on the same track. Ok, it is true, the
"" is treated as text, which is greater than a number. The behaviour with a
genuinely blank cell is different: it is treated as zero.
I don't want to use 0 instead of blank because it will spoil the look of the
program. I only want a number to appear in the cell if certain conditions are
met. If they aren't I want the cell to be blank, not have a zero in there.
I'm sure there is a way around this and will keep looking.
Thanks again,
Frank

 

[Guest]

Perhaps your formula can return a 0, but just display "" to retain the looks
of the project.

Try Format/Cells/Custom and enter
0;-0;""

positive and negative values s/b displayed as such, but 0 s/b displayed as
"". However, the cell will retain the numeric value of 0 so should not
affect your formulae.

 

[Guest]

Yes!!! this works. Thanks very much JMB, this works well and I have learnt
something new.
Ciao, Frank

 

[Guest]

You're welcome.

Yes!!! this works. Thanks very much JMB, this works well and I have learnt
something new.
Ciao, Frank

 

[Harlan Grove]

Frank Winston wrote...

I have a cell formula that returns either a number or "" (blank). This works
fine but when I try to compare the result with another number to see which is
greater, I'm having trouble figuring out how Excel treats the result. If the
formula returned a number (say 2.0) and the other cell contained say 3.0,
then Excel can correctly recognise that 3>2. However, if the result was "",
then it says that "">3. I don't know why this is. You would think that ""
would be treated as zero which would be less than 3, not greater than.

If you're comparing two cells and you *always* want to compare them as
numbers, then ensure you're comparing numbers by wrapping your
references inside N(..) function calls.

=N(X99)>N(Z1000)