Determine RYG ball color based on rows below

Reema Hussain

Hello,

 

I'm trying to write a formula that'll allow me to look at the color of the  RYG balls in some rows, find the lowest color and set that as the main color for the overall project.

 

So, Project A needs a status. I'd like to look at tasks 1-5; if any of them are red, the entire project status needs to be red. If any of the tasks are yellow, the entire project status needs to be yellow and if the tasks are all green, the entire project status needs to be green.

 

I started with this in the cell that represents the entire project status but I got an 'invalid operation' error.

=IF(Status2:Status20 = "Green", "Green").

 

Can someone please help?

 

Thank you,

Reema

Greg Gates

You could definitely do this! I would use the COUNTIF function to count the number of each color and set the current cell appropriately. In this example, I'm setting the color of [Call Status]1 by looking at [Call Status]2:[Call Status]9

 

=IF(COUNTIF([Call Status]2:[Call Status]9, "Red") > 0, "Red", IF(COUNTIF([Call Status]2:[Call Status]9, "Yellow") > 0, "Yellow", "Green"))

 

Basically the formula goes in two steps:

1) If any of these rows is red, make it red. Otherwise, count the yellows.

2) If any of these rows is yellow, make it yellow. Otherwise, make it green.

 

Let me know if something isn't clear, or if that doesn't solve your situation!