Excel Logical Formula

[ju1eshart]

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

[Stefi]

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),"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

[Franz Verga]

Nel post news:[email protected]
*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


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

[David Biddulph]

"ju1eshart" <[email protected]> wrote in message
news:[email protected]...
>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

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

[Dav]

=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

[Don Guillett]

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
[email protected]
"ju1eshart" <[email protected]> wrote in message
news:[email protected]...
>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

[ju1eshart]

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

"Franz Verga" wrote:

> Nel post news:[email protected]
> *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
>
>
> 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
>
>
>

[robert111]

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