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

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

"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 32. 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.

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

"Teethless mama" wrote:

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

"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 32. 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.

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.


"Frank Winston" wrote:

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

"Teethless mama" wrote:

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

"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 32. 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.

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

"JMB" wrote:

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.


"Frank Winston" wrote:

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

"Teethless mama" wrote:

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

"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 32. 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.

You're welcome.

"Frank Winston" wrote:

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

"JMB" wrote:

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.


"Frank Winston" wrote:

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

"Teethless mama" wrote:

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

"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 32. 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.

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)


"Frank Winston" <Frank wrote in message
...
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 32. 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.

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

been dribled2

"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 32. 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.

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 32. 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)