Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1067
  • Last Modified:

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
0
wisemat
Asked:
wisemat
  • 3
  • 3
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
barry houdiniCommented:
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
 
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now