Formula Help

Melissa.Holland80446

Hi, 

I have 6 status columns (harvey ball) that are used to show that status of 4 separate steps to complete work. I then have a 5th column (dropdown currently) that I would like to be automated with a formula to give the overall status of the work being completed.



If all columns are green = "Complete"

If any column is yellow = "In Progress"

If any column is red = "At Risk"

Can someone please help? Thank you!

Eid E. Eid

Maybe not the most elegant solution, but the below will work:

(I have called C1 to C6 the columns containing the Harvey Balls):

=IF([C1]@row = "Red", "At Risk", IF([C2]@row = "Red", "At Risk", IF([C3]@row = "Red", "At Risk", IF([C4]@row = "Red", "At Risk", IF([C5]@row = "Red", "At Risk", IF([C6]@row = "Red", "At Risk", IF([C1]@row = "Yellow", "In Progress", IF([C2]@row = "Yellow", "In Progress", IF([C3]@row = "Yellow", "In Progress", IF([C4]@row = "Yellow", "In Progress", IF([C5]@row = "Yellow", "In Progress", IF([C6]@row = "Yellow", "In Progress", "Complete"))))))))))))

 

L_123

=if(countif([Telco Entry Cable]@row:[Power at Backboard]@row,"Red")>0,"At Risk",if(countif([Telco Entry Cable]@row:[Power at Backboard]@row,"Yellow")>0,"In Progress",if(countif([Telco Entry Cable]@row:[Power at Backboard]@row,"Green")=6,"Complete")))

Paul Newcome

I personally would use something similar to L@123's solution, except I would use CONTAINS to look across the ranges. The formula itself would be very similar to using the COUNTIFS with the exception of "Complete". After establishing the red and yellow, I would just say if there are no blanks in the row, it is complete.

 

=IF(CONTAINS("Red", [Telco Entry Cable]@row:[Power at Backboard]@row), "At Risk", IF(CONTAINS("Yellow", [Telco Entry Cable]@row:[Power at Backboard]@row), "In Progress", IF(CONTAINS("", [Telco Entry Cable]@row:[Power at Backboard]@row) = false, "Complete")))