Formulae Question

lhilton

New Member
Joined
Jan 12, 2004
Messages
9
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.


:oops:
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

lhilton

New Member
Joined
Jan 12, 2004
Messages
9
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
Joined
May 15, 2003
Messages
8,638
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
Joined
Oct 29, 2003
Messages
1,883
hi!
I dont undertand you clearly! but check this out!
Book1
ABCDEFGHI
1projectinitiativesstatusprojectstatusredgreenamber
2a3amberared201
3a4redbamber021
4b2greencgreen020
5b3green
6c3green
7c4green
8a2red
9b4amber
Sheet6
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top