Aggregate Red, Yellow, Green Status to One Overall Grade

Good day,

I am looking to Aggregate Red/Yellow/Green status into one overall R/Y/G. Here is the criteria for grade 1:

All classes are either yellow, or green, overall grade = green
3 classes at yellow, overall grade = yellow
Any one class at red, overall grade = red

Here is what I have tried,

=IF(AND([Grade]@row = "1", (OR([Class 1]@row = "Yellow", [Class 2]@row = "Yellow", [Class 3]@row = "Yellow")), (OR([Class 1]@row = "Green", [Class 2]@row = "Green", [Class 3]@row = "Green"))), "Green")

This is not working as expected. Any thoughts on what I am doing wrong here?

Thank you

Find more posts tagged with

Accepted answers

Ryan Sides

@Marvin Francis

Hi Marvin, you were very close! Since your red requirement overrides all others, you can put it first. Then check for any greens. If not either of those, it's yellow...

=IF(OR([Class1]@row = "Red", [Class2]@row = "Red", [Class3]@row = "Red"), "Red", IF(OR([Class1]@row = "Green", [Class2]@row = "Green", [Class3]@row = "Green"), "Green", "Yellow"))

Please let me know if that works for you!

-Ryan

Ryan Sides

@Marvin Francis Ah ok.

Yes, wrap it in an if statement first.

=IF(Grade@row = "1", IF(OR([Class1]@row = "Red", [Class2]@row = "Red", [Class3]@row = "Red"), "Red", IF(OR([Class1]@row = "Green", [Class2]@row = "Green", [Class3]@row = "Green"), "Green", "Yellow")), "")

Then you could add a portion for Grade 2:

All comments

Ryan Sides

@Marvin Francis

Hi Marvin, you were very close! Since your red requirement overrides all others, you can put it first. Then check for any greens. If not either of those, it's yellow...

=IF(OR([Class1]@row = "Red", [Class2]@row = "Red", [Class3]@row = "Red"), "Red", IF(OR([Class1]@row = "Green", [Class2]@row = "Green", [Class3]@row = "Green"), "Green", "Yellow"))

Please let me know if that works for you!

-Ryan

Marvin Francis

@Ryan Sides I so greatly appreciate this! Thank you

Ryan Sides

@Marvin Francis Happy to help! Glad it worked out. - Ryan

Marvin Francis

@Ryan Sides question, how would I select grade 1 from the first column. Here's what I tried,

=IF(AND([Grade])@row = "1", (IF(OR([Class1]@row = "Red", [Class2]@row = "Red", [Class3]@row = "Red"), "Red", IF(OR([Class1]@row = "Green", [Class2]@row = "Green", [Class3]@row = "Green"), "Green", "Yellow")))).

This gives me an incorrect argument result

Ryan Sides

@Marvin Francis Do you only want it to show up for Grade 1?

Marvin Francis

@Ryan Sides correct, because I have a different R/Y/G requirement for grade 2.

Ryan Sides

@Marvin Francis Ah ok.

Yes, wrap it in an if statement first.

Then you could add a portion for Grade 2:

Marvin Francis

@Ryan Sides this is great, with the second formula I can keep all grades in one column. Thank you!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.