Syntax Help with IF/AND Statement

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

Guest

Hell

Can someone please advise where the syntax error is occuring? I've been on this for sometime and tried various combinations (not very scientific)

=IF(AND(D2>0.5,C5<=0),"Amber",(IF(AND(D2>=1,C5<=0,"Red","Green")

Is there some kind of rational for syntax and MS Excel - if so, I cannot find anything in the help

TIA - Vers
 
=IF(AND(D2>0.5,C5<=0),"Amber",(IF(AND(D2>=1,C5<=0),"Red","Green")))

will not generate an error, but you'll never get "Red", because when D2 >=1
it's already handled in D2>=0.5

Change the order or otherwise simplify your formula. You should be able tot
test C5 just once

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

Verossa said:
Hello

Can someone please advise where the syntax error is occuring? I've been
Click to expand...
on this for sometime and tried various combinations (not very scientific).
 
Hi Vers,

You were lacking a set of brackets.
I have also added an extra condition to the Amber check.
Because D2=0.9 and C5=0 would indicate Amber.
But so does D2=1.9 and C5=0 , although your formula suggests that these
sort of values should indicate Red.

=IF(AND(D2>0.5,D2<1,C5<=0),"Amber",(IF(AND(D2>=1,C5<=0),"Red","Green")))

Cheers
Andy
 
Hi Verossa!

You have answers to your immediate question but...

Re: Is there some kind of rational for syntax?

The conditions for IF functions need to be capable of returning TRUE
or FALSE.

You can pre-test / separately test your conditions by entering them on
their own in cells:

=AND(A1=7,B1<>5)
Returns TRUE if both conditions are met.

With IF functions Excel works from left to right. If a condition is
met then that same condition will not be addressed again:

Example:
=IF(A1<10,"Less than 10",IF(A1=7,"Seven","Not Seven"))

If A1 is 7, then this formula will return "Less than 10" because the
case of A1=7 is covered by A1<10.

You might find the logic used is analogous to that of decision trees.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
=IF(C5>0,"Red",IF(D2>=1,"Amber","Green"))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

Verossa said:
Hi Niek

How would I tot test C5 just the once as opposed to multiply as is the
Click to expand...
case with my formula? I'd appreciate some insights.
 
Back
Top