Formulae Question

lhilton

New Member
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.

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

lhilton

New Member
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

MrExcel MVP
Hi,

All RAG must be filled in.
Book1
ABCDEF
1ProjInitiativeRAG
2aa1RaR
3aa2AbA
4aa3GcG
5bb1G
6bb2G
7bb3A
8cc1G
9cc2G
10cc3G
Sheet1

SIXTH SENSE

Well-known Member
hi!
I dont undertand you clearly! but check this out!
Book1
ABCDEFGHI
1projectinitiativesstatusprojectstatusredgreenamber
2a3amberared201
3a4redbamber021
4b2greencgreen020
5b3green
6c3green
7c4green
8a2red
9b4amber
Sheet6

MrExcel MVP
lhilton said:
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
ABCDEF
1ProjectInitiativeRAGaAmber
2a1GreenbGreen
3c2RedcRed
4a3Green
5b4Green
6c5Green
7b6Green
8b7Green
9a8Amber
10b9Green
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.

Replies
15
Views
521
Replies
1
Views
212
Replies
6
Views
471
Replies
7
Views
541
Replies
3
Views
196

1,171,073
Messages
5,873,646
Members
432,991
Latest member
Nuppu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back