Solved

# Determine overal RAG status from list of task status

Posted on 2010-11-29
1,042 Views
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
Question by:wisemat
• 3
• 3

LVL 50

Expert Comment

ID: 34232856
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

Author Comment

ID: 34232879
Oops, attached now
example1.xlsx
0

LVL 50

Expert Comment

ID: 34232982
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

Author Comment

ID: 34233033
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

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 34233398
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

Author Closing Comment

ID: 34233452
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

### Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.