Formula to show specific symbols based on multiple criteria

Hello, I hope some of you fine folk might be able to help me.

I am tracking the results of three separate tests and I want to create a column that will show either a Green, Amber or Red based on the results.

See screenshot below. There is a checkbox to highlight if a test has been completed and then a dropdown to show if it was a pass, fail or to be discussed.

Then in the Flag Column I want to to show a symbol based on the results within the three Pass/Fail Columns as is currently shown but was completed manually:

I have manage to get the below formula to work that indicates the status of a row based on the percentage of tests complete and the due date for them to be completed:

=IF([Parent?]@row = 1, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", "Green"))), IF([% Completed]@row = 1, "Green", IF(AND(Finish@row < TODAY(), [% Completed]@row < 1), "Red", IF([% Completed]@row = 0, "Blue", "Yellow"))))

Using this I have tried to change the formula to do what I require for the Pass/Fail flag, but I'm struggling to figure out what I need to change.

Any help you could provide would be hugely appreciated!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!