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

F

Frank Winston

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.
 
P

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)
 
T

Teethless mama

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

Frank Winston

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
 
J

JMB

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.
 
F

Frank Winston

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

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.
Click to expand...

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)
 
Top