Conditional Formatting Formula By Using Colors In the Cells

As discussed, I have listed and numbered the business rules, it is worth creating this in a sequenced query from 1 to 10. Please note the & conditions in rules 4 and 9:

1. If any HIGH CRITICAL station is RED, then group = RED.
2. If greater than or equal to 50% of the HIGH CRITICAL stations are AMBER (and no less than 2) then Group = RED
3. If greater than or equal to 70% of MEDIUM CRITICAL stations are RED 9 (and no less than 3) then Group = RED
4. If less than 50% of HIGH CRITICAL stations are AMBER & greater than or equal to 70% of MEDIUM CRITICAL stations are AMBER then Group = RED
5. If any HIGH CRITICAL station is AMBER, then Group = AMBER
6. If any MEDIUM CRITICAL station is RED, then Group = AMBER
7. If greater than or equal to 50% of MEDIUM CRITICAL stations are AMBER (and no less than 3), then Group = AMBER
8. If greater than or equal to 70% of LOW CRITICAL stations are RED (and no less than 2), then Group = AMBER
9. If less than 50% of MEDIUM CRITICAL stations are AMBER & greater than or equal to 70% of LOW CRITICAL stations are AMBER then Group = AMBER
10. All other conditions that do not fit into the above scenarios, the Group = GREEN

Thanks for your help.

Regards,
shameen

Hello JeteMc, Thank you very much for your kind reply. The result required in one cell (Resul???) for the criticality and integrity status of each line items. For eg. If any critical stations are red and the result will be red which will return on the result field and the rest of the scenarios will be ignored. Similarly, on the second scenario, if greater than or equal to 50% of the high critical stations are amber then it should return the red on the result cell and the rest of the scenarios will be ignored. This will help us to mitigate the risk from each stations. Is there any way that the following 10 conditions result can return in one cell. Basically it is very critical to identify the high critical stations.

Thank you very much for your kind reply. I definitely owe you a gift!!!

Kind Regards,
Shameen
1. If any HIGH CRITICAL station is RED, then group = RED.
2. If greater than or equal to 50% of the HIGH CRITICAL stations are AMBER (and no less than 2) then Group = RED
3. If greater than or equal to 70% of MEDIUM CRITICAL stations are RED 9 (and no less than 3) then Group = RED
4. If less than 50% of HIGH CRITICAL stations are AMBER & greater than or equal to 70% of MEDIUM CRITICAL stations are AMBER then Group = RED
5. If any HIGH CRITICAL station is AMBER, then Group = AMBER
6. If any MEDIUM CRITICAL station is RED, then Group = AMBER
7. If greater than or equal to 50% of MEDIUM CRITICAL stations are AMBER (and no less than 3), then Group = AMBER
8. If greater than or equal to 70% of LOW CRITICAL stations are RED (and no less than 2), then Group = AMBER
9. If less than 50% of MEDIUM CRITICAL stations are AMBER & greater than or equal to 70% of LOW CRITICAL stations are AMBER then Group = AMBER
10. All other conditions that do not fit into the above scenarios, the Group = GREEN

Hi, Is there any way thaAttachment 500593t I can share the full file with you .

The results of the above 10 conditions required on the below highlighted box..

Regards,
Shameen

Hi, Sorry for the mess. Further to the previous communication. The true or false statement doesn't help to analyse the risk of each station. Only way it will help to anlyse the risk factor of each station is to compare the relation between the criticality and integrity status by applying the 10 business rules. This means that the criticality and ingrity columns will need to return the result in one cell by considering the 10 rules. I am also attaching the algorithm of the result which I am looking for to get.

Kind Regards,
Shameen

Yes sir, the results required on the raw 20. At the same time the criticality doesnt change because thats the station risk fixed to each station. only the the integrity status changes. So the result will be cirticality Vs. integrity. For eg. For this week, the integrity status of 3 low critical ciritical stations (Field Name: Criticality) are red due to the critical lifting and the integrity status of 5 Medium critical stations are red, then it should change accordingly by applying 10 business rules.

Thanks for your kind support.

Regards,
Shameen

You are awesome! Genius, No words to describe!!! In the formula you have share all the conditions words except line item #. 4 and #6 as copied below. Is there any that I can include the below as well. Attached here with the test results of the formulas you have made

4. If less than 50% of HIGH CRITICAL stations are AMBER & greater than or equal to 70% of MEDIUM CRITICAL stations are AMBER then Group = RED

5. If any MEDIUM CRITICAL station is RED, then Group = AMBER

Great job!!!

Regards,
Shameen

Hello JetMc, Thanks for the formula. I found some minor error when applying the formulas. Can you kindly help to sort the points stated on the attached file.

Regards,
Shameen

Further to the post no. 10, I also made another file and made the test. In fact, i am stuck in between and the result attached for your kind information.

Regards,
shameen