If And Statement Issue

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

Find more posts tagged with

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.