Formulae Question

[lhilton]

I have a similar question to one I posted yesterday.

I have 3 projects that I am currently managing a, b and c. The projects have 3, 4 and 2 initiatives running within them respectively and each of the initiatives has a RAG status (red, amber or green). The project code (a, b or c) is given in cells A1:A9 and is not neccesarily sorted. The corresponding initiative number is listed in cells B1:B9. Cells C1:C9 contain the corresponding RAG status for each initiative. Cells E1:E3 list the projects a, b and c. In cells F1:F3 I am trying to return a rag status for each of the projects based on the following logic:

If any of the initiatives within the project has a 'red' status, that project should aslo have a 'red' status. If none of the initiatives has a 'red' status, but at least one 'amber' status, that project should have an 'amber' status. If all of the initiatives within a project have a 'green' status, that project should also have a 'green' status.

 

[Aladin Akyurek]

Care to post your data?

 

[lhilton]

Here's the simplified example:

Project Initiative RAG
a 1 Green
c 2 Red
a 3 Green
b 4 Green
c 5 Green
b 6 Green
b 7 Green
a 8 Amber
b 9 Green

 

[fairwinds]

Hi,

All RAG must be filled in.

Book1
	A	B	C	D	E	F
1	Proj	Initiative	RAG
2	a	a1	R		a	R
3	a	a2	A		b	A
4	a	a3	G		c	G
5	b	b1	G
6	b	b2	G
7	b	b3	A
8	c	c1	G
9	c	c2	G
10	c	c3	G
Sheet1

 

[SIXTH SENSE]

hi!
I dont undertand you clearly! but check this out!

Book1
	A	B	C	D	E	F	G	H	I
1	project	initiatives	status		project	status	red	green	amber
2	a	3	amber		a	red	2	0	1
3	a	4	red		b	amber	0	2	1
4	b	2	green		c	green	0	2	0
5	b	3	green
6	c	3	green
7	c	4	green
8	a	2	red
9	b	4	amber
Sheet6

 

[Aladin Akyurek]

Here's the simplified example:

Project Initiative RAG
a 1 Green
c 2 Red
a 3 Green
b 4 Green
c 5 Green
b 6 Green
b 7 Green
a 8 Amber
b 9 Green

Although not fully tested...

Book2
	A	B	C	D	E	F
1	Project	Initiative	RAG		a	Amber
2	a	1	Green		b	Green
3	c	2	Red		c	Red
4	a	3	Green
5	b	4	Green
6	c	5	Green
7	b	6	Green
8	b	7	Green
9	a	8	Amber
10	b	9	Green
Sheet1

The formula in F1 is:

=INDEX({"Red","Amber","Green"},MATCH(TRUE,ISNUMBER(MATCH({"Red","Amber","Green"},IF($A$2:$A$10=E1,$C$2:$C$10,""),0)),0))

which must be confirmed with control+shift+enter instead of just with enter.