If And Statement Issue

VirtixIT

Smartsheet Community:

I am Trying Have My Rag Change to Not Started When The Following Conditions Are Met and Add it to My Existing Formula:

IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started", "Not Started"

I Keep Receiving a Green Instead of Not Started. Her is My Complete Formula Below:

=IF([% Complete (Actual)]@row = 1, "Blue", IF([Schedule Variance]@row = 0, "Green", IF([Schedule Variance]@row > 0, "Green", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started", "Not Started", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))))

Thanks

VirtixIT

I Figured it Out. I Had to Change The Order to a Higher Condition. See Below:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started"), "Not Started", IF([Schedule Variance]@row >= 0, "Green", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))

heyjay

=

IF([% Complete (Actual)]@row = 1, "Blue",

IF([Schedule Variance]@row >= 0, "Green",

IF(AND([Schedule Variance]@row = 0,[Progress Status]@row = "Not Started"), "Not Started",

IF([Schedule Variance]@row <= -7, "Red", "Yellow")

Can you please try this one.

We can merge your Green condition using >=

Added missing parethesis for the AND function.

VirtixIT

https://community.smartsheet.com/discussion/comment/435491#Comment_435491

I am Still Receiving a Green Symbol Instead of "Not Started". Also I Merged The Greater Than = To

bisaacs

Hey @VirtixIT,

It looks like your IF/AND statement, if it returns true, isn't a color option for the circle. Also, because you put the AND after the IF([Schedule Variance]@row = 0, "Green"), the formula is going to process that condition before the AND statement. Try this:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started"), "Yellow", IF([Schedule Variance]@row >= 0, "Green", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))

hope this helps!

VirtixIT

I Figured it Out. I Had to Change The Order to a Higher Condition. See Below:

=IF([% Complete (Actual)]@row = 1, "Blue", IF(AND([Schedule Variance]@row = 0, [Progress Status]@row = "Not Started"), "Not Started", IF([Schedule Variance]@row >= 0, "Green", IF([Schedule Variance]@row <= -7, "Red", "Yellow"))))