RYG formula question

I need some help with tweaking the below:

=IF(OR([Actual Completion Percentage %]@row = 1, [Schedule Variance]@row >= 0.9, AND([Actual Completion Percentage %]@row >= 0, [Baseline Start]@row > TODAY())), "Green", IF(OR([Schedule Variance]@row >= 0.8, IF(OR([Schedule Variance]@row < 0.9, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Yellow", AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red", IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red", IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), "")))))

I am trying to state: Green is greater than 0.9, yellow is between 0.8 and 0.9, and red is anything less than 0.8

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Melissa Torrez

I find it helps to break out IF statements into individual "sentences" to see where there may be an issue. The way I do this is by putting each "IF" on its own line.

For example, I did this with yours and bolded where I think there may be a mixup of logic statements:

=IF(OR([Actual Completion Percentage %]@row = 1, [Schedule Variance]@row >= 0.9, AND([Actual Completion Percentage %]@row >= 0, [Baseline Start]@row > TODAY())), "Green",

IF(OR([Schedule Variance]@row >= 0.8,

IF(OR([Schedule Variance]@row < 0.9, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Yellow",

AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red",

IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red",

IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), "")))))

Try this instead:

=IF(OR([Actual Completion Percentage %]@row = 1, [Schedule Variance]@row >= 0.9, AND([Actual Completion Percentage %]@row >= 0, [Baseline Start]@row > TODAY())), "Green",

IF(AND([Schedule Variance]@row >= 0.8, [Schedule Variance]@row < 0.9, [Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Yellow",

IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red",

IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), ""))))

Let me know if that solved the problem!

Cheers,

Genevieve

Julio S.

HI @Melissa Torrez,

For the statement at the bottom of your post, you can try with =IF([Schedule Variance]@row < 0.8, "Red", IF([Schedule Variance]@row < 0.9, "Yellow", "Green")) and then add complexity to the formula with additional conditions as needed.

I hope this can also be of help!

Julio

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.