Conditional formatting

[edwardj3]

I have a scoring matrix for a number of services. Each service has its own RAG status. Example below.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Minutes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]<30[/TD]
[TD]30-59[/TD]
[TD]60-89[/TD]
[TD]90-119[/TD]
[TD]120-149[/TD]
[TD]150-179[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Serv1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Serv2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Serv3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Serv4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

I am using the code below to reference the table above and then place the appropriate score in in the results column - AB.

=INDEX($B$4:$M$9,MATCH(P4,$B$4:$B$9,0),MATCH(IF(AA4<30,1,IF(AND(AA4>=30,AA4<=59),2,IF(AND(AA4>=60,AA4<=89),3,IF(AND(AA4>=90,AA4<=119),4,IF(AND(AA4>=120,AA4<=149),5,IF(AND(AA4>=150,AA4<=179),6,IF(AND(AA4>=180,AA4<=209),7,IF(AND(AA4>=210,AA4<=239),8,IF(AND(AA4>=240,AA4<=269),9,10))))))))),$B$4:$M$4,0))

In my example, P4 is where for example Serv1 is placed and AA4 is where the minutes are.

Is it possible to set up conditional formatting so that either the score in column AB2:1000 is showing the appropriate RAG colour or the the cell with the score in is the appropriate RAG colour and the score figure remains in black text? So If Serv1 was out for 72mins, applying the scoring matrix would make this an amber score of 9, Serv2 out for 162 mins would be a red score of 18 etc.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Service[/TD]
[TD]Mins[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]Serv1[/TD]
[TD]72[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Serv2[/TD]
[TD]162[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]Serv3[/TD]
[TD]49[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

I have applied formula in conditional formatting which works on single cell but can't be applied to all cells in the column.

IF(P4="Serv1",(IF(AND(AB4>=6,AB4<=9),"True","False"))) this turns the cell orange/amber but I can find a way of applying this down the column as the service description changes and also each service has its own RAG rating.

Any help would be appreciated. Happy to provide further details if required.

 

[Special-K99]

Formulas can't read the colour of cells, you need VBA for that.

Are you saying the colours of the columns in the first table never change?

By the way, you can reaplce ALL those IF(AND(AA4 >= ...,AA4<=...) simply with this

=IF(AA4>=270,10,INT(AA4/30)+1)

If you look at the upper end of the IF statement for AA4 it's always a multiple of 30.
This assumes AA4 will awlays never be negative

 

[edwardj3]

Thanks for the advice about the IF(AND(... formula. I'll amend that.

The RAG values and colours in the first table will always stay the same as this is the scoring . What does change is the service and/or the mins value.

If Serv1 was unavailable for 100mins, this would score as 12 and would be a red rating but if Serv2 was also unavailable for 100mins this would also score 12 but would have an amber rating. A 100min outage for Serv4 would score 4 and have a green rating. So there are multiple combinations. The RAG colour/status to be applied is dependent on what service is unavailable and also the number of minutes unavailable. I then use the Index,Match,Match formula to identify the score in the first table.

Hope that helps.

 

[Special-K99]

I think you should make the table easier to extract data.
Place the lower minutes in row 1 and the upper end minutes in row 2

Like this:

In a blank spreadsheet paste this table so you get
"x" in A1 and
72 in B9

x		0	30	60	90	120	150
		29	59	89	119	149	179
		1	2	3	4	5	6
Serv1	3	3	6	9	12	15	18
Serv2	3	3	6	9	12	15	18
Serv3	2	2	4	6	8	10	12
Serv4	1	1	2	3	4	5	6
							
Serv1	72	9		9			
Serv2	162	18		18			
Serv3	49	4		4

Now you can use this formula to return the correct result

=INDEX(C$4:H$7,MATCH(A9,A$4:A$7,0),MATCH(B9,C$1:H$1,1))
and copy down column D

Working on the colour formatting...

 

[Special-K99]

Ok, here's one way

Create a table thus:

11 G
21 G
22 G
31 G
32 G
33 G
41 G
42 G
43 G
44 G
12 Y
13 Y
23 Y
24 Y
34 Y
35 Y
45 Y
46 Y
14 R
15 R
16 R
25 R
26 R
36 R

If you now do a VLOOKUP of this value

MATCH(A9,A$4:A$7,0)*10+MATCH(B9,C$1:H$1,1)

in that table you can return the colour code R Y or G and colour the cell with the correct colour.
So something like

=VLOOKUP(MATCH(A9,A$4:A$7,0)*10+MATCH(B9,C$1:H$1,1),Sheet2!A$1:B$24,2)="R"
format as red

=VLOOKUP(MATCH(A9,A$4:A$7,0)*10+MATCH(B9,C$1:H$1,1),Sheet2!A$1:B$24,2)="G"
format as green

=VLOOKUP(MATCH(A9,A$4:A$7,0)*10+MATCH(B9,C$1:H$1,1),Sheet2!A$1:B$24,2)="Y"
format as yellow

Alternatively on another sheet create a table 6 columns by 4 matching your first table with the letters R Y and G in the approrpaite cells.

Then just use the same INDEX(MATCH(), MATCH()) you used earlier but on this table to return the correct colour letter R Y or G.

So instead of VLOOKUP you'd have

=INDEX(Sheet2:...MATCH(...),MATCH(...))="R"
format as red

etc

 

[edwardj3]

Thanks for your help Special-K99