edwardj3

New Member
Joined
Jan 16, 2018
Messages
30
I have a scoring matrix for a number of services. Each service has its own RAG status. Example below.

Minutes
<3030-5960-8990-119120-149150-179
123456
Serv13369121518
Serv23369121518
Serv3224681012
Serv41123456

<tbody>
</tbody>

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.

ServiceMinsResult
Serv1729
Serv216218
Serv3494

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
 
Last edited:

edwardj3

New Member
Joined
Jan 16, 2018
Messages
30
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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

Code:
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...
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Ok, here's one way

Create a table thus:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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