Aggregate Red, Yellow, Green Status to One Overall Grade

Marvin Francis

Good day,

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

1. All classes are either yellow, or green, overall grade = green
2. 3 classes at yellow, overall grade = yellow
3. 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

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:

=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")), IF(Grade@row = "2", do what you need to do here))

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.

=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:

=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")), IF(Grade@row = "2", do what you need to do here))

Marvin Francis

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