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
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
Comments

=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

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives