Aggregate Red, Yellow, Green Status to One Overall Grade

edited 10/27/22

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

• ✭✭✭✭✭✭

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

Come Say Hello!

• ✭✭✭✭✭✭

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

=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

Come Say Hello!

• ✭✭✭✭✭✭

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

Come Say Hello!

• @Ryan Sides I so greatly appreciate this! Thank you

• ✭✭✭✭✭✭

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

Ryan Sides

Come Say Hello!

• @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

• ✭✭✭✭✭✭

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

Ryan Sides

Come Say Hello!

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

• ✭✭✭✭✭✭

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

=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