Excel Logical Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5>=3162277),"Red",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:
 
Nel post *ju1eshart* ha scritto:
I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5>=3162277),"Red",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
Click to expand...


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 said:
I have the following formula
=IF(AND(J5<316226),"Green",IF(AND(J5>=3162277),"Red",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
Click to expand...

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?
 
=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

You do not need the AND statements in your original expression

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

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

If it is just the only situation left
Regards

Dav
 
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
(e-mail address removed)
 
Thanks Franz. Your suggestion worked the best. Your help is much appreciated.
Jules - Scotland
 
=IF(K1="","no data",IF(K1<5,"green",IF(AND(K1>4,K1<9),"amber","red")))


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