Minutes | |||||||

<30 | 30-59 | 60-89 | 90-119 | 120-149 | 150-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 |

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

Service | Mins | Result |

Serv1 | 72 | 9 |

Serv2 | 162 | 18 |

Serv3 | 49 | 4 |

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