I have an 'if' problem which I don't understand and cannot solve.

Cell H2 contains a formula delivering either a value (0) or "".
The formula in I2 reads '=IF(VALUE(H2)0,"sold","-")'. This works
correctly if value(H2)0, but otherwise returns '#VALUE!'.

Thanks in advance for any help to what must be a simple problem.

Kanga 85;173051 Wrote:
Cell H2 contains a formula delivering either a value (0) or "".
The formula in I2 reads '=IF(VALUE(H2)0,"sold","-")'. This works
correctly if value(H2)0, but otherwise returns '#VALUE!'.

Thanks in advance for any help to what must be a simple problem.

If the formula in H2 shows the text string "0" (without the quotes),
then your IF formula needs to treat that cell as text. Whatever is
showing in cell H2 needs to be EXACTLY tested. So if it's 0 then test
for "0", if it shows (0), then test for "(0)".

=IF(H2="0","sold","-")
or
=IF(H2="(0)","sold","-")

Now, if you change the formula in H2 to just result in numbers, then
your original formula will work. So look at that possibility, too.

Can you show me the formula from H2?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47936

This should suffice:
=IF(OR(H2="",H2<=0),"-","sold")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Kanga 85" wrote:
I have an 'if' problem which I don't understand and cannot solve.

Cell H2 contains a formula delivering either a value (0) or "".
The formula in I2 reads '=IF(VALUE(H2)0,"sold","-")'. This works
correctly if value(H2)0, but otherwise returns '#VALUE!'.

Thanks in advance for any help to what must be a simple problem.

Replace the earlier with this:
=IF(OR(H2="",H2+0<=0),"-","sold")
since there's hints that H2 might return text nums
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Try this:

=IF(N(H2)0,"Sold","-")

--
Biff
Microsoft Excel MVP


"Kanga 85" wrote in message
...
I have an 'if' problem which I don't understand and cannot solve.

Cell H2 contains a formula delivering either a value (0) or "".
The formula in I2 reads '=IF(VALUE(H2)0,"sold","-")'. This works
correctly if value(H2)0, but otherwise returns '#VALUE!'.

Thanks in advance for any help to what must be a simple problem.

My interpretation of the OP is that H2 will contain either a formula blank
or a number.

=IF(OR(H2="",H2+0<=0),"-","sold")

If H2 ="" then H2+0 will cause a #VALUE! error.


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Replace the earlier with this:
=IF(OR(H2="",H2+0<=0),"-","sold")
since there's hints that H2 might return text nums
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

If H2 ="" then H2+0 will cause a #VALUE! error.
Good point. I overlooked that.
Dismiss my 2nd response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---

Thanks to you all for your useful replies. The problem is now solved, and
you have provided me with further insight. The formula in H2 is:
=IF(F20,D2*G2,"")


"JBeaucaire" wrote:


Kanga 85;173051 Wrote:
Cell H2 contains a formula delivering either a value (0) or "".
The formula in I2 reads '=IF(VALUE(H2)0,"sold","-")'. This works
correctly if value(H2)0, but otherwise returns '#VALUE!'.

Thanks in advance for any help to what must be a simple problem.

If the formula in H2 shows the text string "0" (without the quotes),
then your IF formula needs to treat that cell as text. Whatever is
showing in cell H2 needs to be EXACTLY tested. So if it's 0 then test
for "0", if it shows (0), then test for "(0)".

=IF(H2="0","sold","-")
or
=IF(H2="(0)","sold","-")

Now, if you change the formula in H2 to just result in numbers, then
your original formula will work. So look at that possibility, too.

Can you show me the formula from H2?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47936