I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5=3162277),"Re d",IF(AND(J5=316228),"Amber",IF(AND(J5<=3162276), "Amber"))))
I'd like to include the comment "NO DATA" if the ref cell is not populated
with a value. I thought " " ment empty cell but I can't get it to work.
Thanks in advance for your help

Try "" (without space between quotation marks) or function ISBLANK(A1)!

Regards,
Stefi



€žju1eshart€� ezt Ã*rta:

I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5=3162277),"Re d",IF(AND(J5=316228),"Amber",IF(AND(J5<=3162276), "Amber"))))
I'd like to include the comment "NO DATA" if the ref cell is not populated
with a value. I thought " " ment empty cell but I can't get it to work.
Thanks in advance for your help

Nel post
*ju1eshart* ha scritto:

I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5=3162277),"Re d",IF(AND(J5=316228),"Amber",IF(AND(J5<=3162276), "Amber"))))
I'd like to include the comment "NO DATA" if the ref cell is not
populated with a value. I thought " " ment empty cell but I can't get
it to work. Thanks in advance for your help


Try this way:

=IF(J5="","",IF(J5<316226,"Green",IF(J5=3162277," Red",IF(J5=316228,"Amber",IF(J5<=3162276,"Amber") ))))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

"ju1eshart" wrote in message
...
I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5=3162277),"Re d",IF(AND(J5=316228),"Amber",IF(AND(J5<=3162276), "Amber"))))
I'd like to include the comment "NO DATA" if the ref cell is not populated
with a value. I thought " " ment empty cell but I can't get it to work.
Thanks in advance for your help

If the cell is empty, try "", not " ".
What are your AND functions trying to do, as in each case there seems to be
only one argument?
--
David Biddulph

=IF(AND(J5<316226),"Green",IF(AND(J5=3162277),"Re
d",IF(AND(J5=316228),"Amber",IF(AND(J5<=316227 6), "Amber"))))
I'd like to include the comment "NO DATA" if the ref cell is not
populated
with a value. I thought " " ment empty cell but I can't get it to
work.
Thanks in advance for your help

You do not need the AND statements in your original expression

IF(J5<316226,"Green",IF(J5=3162277,"Red",IF(J5=3 16228,"Amber",IF(J5<=3162276,
"Amber",if(isblank(j5),"NO DATA"," ")))))

or even
IF(J5<316226,"Green",IF(J5=3162277,"Red",IF(J5=3 16228,"Amber",IF(J5<=3162276,
"Amber","NO DATA"," "))))

If it is just the only situation left
Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=555519

I am also confused about what you are doing but try this idea
=if(and(j5=316226,,j5<3162276),"amber",next if)

Don Guillett
SalesAid Software

"ju1eshart" wrote in message
...
I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5=3162277),"Re d",IF(AND(J5=316228),"Amber",IF(AND(J5<=3162276), "Amber"))))
I'd like to include the comment "NO DATA" if the ref cell is not populated
with a value. I thought " " ment empty cell but I can't get it to work.
Thanks in advance for your help

Thanks Franz. Your suggestion worked the best. Your help is much appreciated.
Jules - Scotland

"Franz Verga" wrote:

Nel post
*ju1eshart* ha scritto:

I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5=3162277),"Re d",IF(AND(J5=316228),"Amber",IF(AND(J5<=3162276), "Amber"))))
I'd like to include the comment "NO DATA" if the ref cell is not
populated with a value. I thought " " ment empty cell but I can't get
it to work. Thanks in advance for your help


Try this way:

=IF(J5="","",IF(J5<316226,"Green",IF(J5=3162277," Red",IF(J5=316228,"Amber",IF(J5<=3162276,"Amber") ))))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

=IF(K1="","no data",IF(K1<5,"green",IF(AND(K14,K1<9),"amber","r ed")))


this gives you no data if cell k1 is empty, and either red or amber or
green according to the value of k1................


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=555519