Determine overal RAG status from list of task status

Hello Experts
I wonder if you can help with this puzzle.

I have a list of tasks in excel, and each task has a RAG (Red/Amber/Green) status
I would like excel to determine my 'overall' status based on the status that is most prevellent from the list of task statuses (is that a word?)

I can't think of a good way of doing this, I tried to give a weight to each task and score the result but I dont think will work well.

Can you help?  I have attached a spreadsheet that should help explain what I mean
Thanks
wisematAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Hello wisemat, The formula is an "array formula" and you need that key combination for it to work.

Put formula in I30, press F2 key (to select formula) then hold down CTRL and SHIFT and press ENTER. If done correctly then curly braces like { and } will appear around the formula in the formula bar, see attached.

regards, barry
26645279.xlsx
0
 
barry houdiniCommented:
I can't see an attachment, wisemat

Is Red/Amber/Green a text value or an actual colour here?

Assuming text you could use this formula to get the most common value

=INDEX(A2:A100,MODE(IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))))

confirmed with CTRL+SHIFT+ENTER

barry
0
 
wisematAuthor Commented:
Oops, attached now
 example1.xlsx
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
barry houdiniCommented:
So what result do you want? If you just want the moost common text then you can apply the formula I suggested above to range O4:O29, i.e.

=INDEX(O4:O29,MODE(IF(O4:O29<>"",MATCH(O4:O29,O4:O29,0))))

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
wisematAuthor Commented:
Hi Barry
I copied the formula into O30 and I get a #Value error
Also I am not sure what you mean by 'confirmed with CTRL+SHIFT+ENTER'

Sorry for not grasping this right away.  I think using most common result is what im after

Thanks again
0
 
wisematAuthor Commented:
Spot on barry, thank you for this.

To close off, is it simple to adapt it so that it goes with the most common response UNLESS there is a red in which case it would always give a red response?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.