Multi column evaluation to set RAG type status

DavidS

Hello everyone,

 

I'm beating my head against the wall at this point trying to figure out how to make this work.

I have a project tracker - each row is an over view for a given project.

In my case I have 8 columns for status that are all set to High, Medium, Low (cued RAG but not balls - conditional formatting for the color with Text).  

I want to evaluate across the row for a Summary column item - If there is a High in any of the 8 columns I want this one to write High to the summary, Medium and Low would do the same.

 

I can do it for a single cell, but not for all cells in the range.

 

This line works:

=IF(Col2 = "High", "High", IF (Col2 = "Medium", "Medium", "Low"))

 

This doesn't work

=IF(Col2:Col9= "High", "High", IF(Col2:Col9 = "Medium", "Medium", "Low"))

 

 

Would I need to do something along the lines of the below to account for all 8? 

=IF(Col2 = "High", "High", IF (Col2 = "Medium", "Medium", "Low")) OR (Col3 = "High", "High", IF (Col3 = "Medium", "Medium", "Low")) OR ... OR ...

 

The goal is if there is a High anyway on a row it should return High, If that is not true, then if there is a  Medium anywhere then Medium, and if neither of those to cases are true it should return Low.

 

Ideas? Help?   

I would rather automate this, than have to work through each deliverable item and update it manually as various pariticpants update the cells randomly through the week - which could result in missed values

 

Thanks,

David

 

Terry Frank

Assuming the status data is in Column2, perhaps counting the occurences might work for you:

 

=IF(COUNTIF([Column2]3:[Column2]13, "High") > 0, "High", IF(COUNTIF([Column2]3:[Column2]13, "Medium") > 0, "Medium", "Low"))

 

Good Luck.

Terry

Travis

Terry is spot on - using the COUNTIF formula will get you the results you are looking for. 

 

If you are using hierarchies, you could use this in the parent row which will automatically calculate a result based off its children:

 

=IF(COUNTIF(CHILDREN(), “High”) > 0, “High”, IF(COUNTIF(CHILDREN(), “Medium”) > 0, “Medium”, IF(COUNTIF(CHILDREN(), “Low”) > 0, “Low”)))

 

Adding that last IF statement will make make the result blank if all the children are blank vs your formula Terry which will display “Low” if all the cells in the range are blank.

 

DavidS

Thanks so much.  It's easy to decide on an approach and not consider alternatives.  Time to do some cut and paste work