#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# If / Or with RGYG

edited 12/09/19

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

Liz

Tags:

• =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
• Employee

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

This discussion has been closed.