If / Or with RGYG

Itwasreallyfun

Hi All

I am trying to write a formula that shows whether a student is competent. Some units have 3 assessments (A, B, C) and some have 4 (A, B, C, D). I am using the Grey ball to show no assessment or N/A (for the units that only have 3 assessments). 

• 3 assessments - A, B, C = green and D =  Grey then the student is CO, otherwise they are NYC
• 4 Assessment - A, B, C, D = green, then the student is CO, otherwise they are NYC

 

I think it needs to be an IF/Or, but I am lost as to how to write it. 

This formula works for 4 assessments but not for 3. 

=IF([Assessment A]10 = "Green", IF([Assessment B]10 = "Green", IF([Assessment C]10 = "Green", IF([Assessment D]10 = "Green", "CO", "NYC"))))

This is what I tried to convert it to........

=IF(AND(OR[Assessment D]28 = "Green", [Assessment D]28 = "Gray"), [Assessment A]28 = "Green", [Assessment B]28 = "Green", [Assessment C]28 = "Green", "CO", "NYC"))))

 

Can anyone please help?

 

Liz

MattH

=IF(AND(A1 = "Green", B1 = "Green", C1 = "Green", D1 = "Green"), "CO", IF(AND(A1 = "Green", B1 = "Green", C1 = "Green", NOT(D1 = "Green")), "CO", "NYC"))

 

• If all four are green, the result is CO
• If Three are green, and the fourth one is not Gray, the result is CO (A blank four slot will mean CO)
• Any other condition is NYC

Taylor F

Hello Liz, 

If the last assessment isn't always going to be the one that the students don't complete, you will want to create a formula that is more flexible. Also, if you ever add additional assessments the formula won't break. 

=IF(COUNT([Assessment A]1:[Assessment D]1) = (COUNTIF([Assessment A]1:[Assessment D]1, "Grey") + COUNTIF([Assessment A]1:[Assessment D]1, "Green")), "CO", "NYC")

What this formula does is count how many assessments you have and then compare it to how many green or gray balls there are. 

Let me know if you have any questions.

Taylor