Conditional Formatting Formula By Using Colors In the Cells

This proposed solution uses nine formulas which will return either TRUE or FALSE for the first nine scenario's. It does seem like scenarios 3 and 4 could be combined because it is possible, in #4, that there could be 0% of high critical stations are amber and 70% or more of medium critical stations are red, which would be basically the same as scenario #3 with the exception of the minimum requirement, therefore dropping the minimum requirement from #3 seems to cover number 4. It also seems that #9 could trigger an amber group rating at a lower threshold than #8. That said I went ahead and wrote formulas to cover the scenarios as they are presented in the first post. In the attached file column H lists the scenarios and column G displays the results of the formulas so that it is easy to see which scenario(s) lead(s) to the group condition displayed in C10. The following three formulas are used as conditional formatting rules for filling cell C10 Green, Amber and Red respectively:

Formula:

=COUNTIF(G14:G22,TRUE)=0

Formula:

=COUNTIF(G18:G22,TRUE)>0

Formula:

=COUNTIF(G14:G17,TRUE)>0

Let us know if you have any questions.

If I understand correctly the color needs to be applied to the merged cells in row 20. There are two conditional formatting rules based on the following formulas. For Red fill:

Formula:

=OR(COUNTIFS(AA$14:AA$64,"H",AB$14:AB$64,"Red")>0,IFERROR(AND(COUNTIFS(AA$14:AA$64,"H",AB$14:AB$64,"Amber")>=2,COUNTIFS(AA$14:AA$64,"H",AB$14:AB$64,"Amber")/COUNTIF(AA$14:AA$64,"H")>=0.5),FALSE),IFERROR(AND(COUNTIFS(AA$14:AA$64,"M",AB$14:AB$64,"Red")>=3,COUNTIFS(AA$14:AA$64,"M",AB$14:AB$64,"Red")/COUNTIF(AA$14:AA$64,"M")>=0.7),FALSE),IFERROR(AND(COUNTIFS(AA$14:AA$64,"H",AB$14:AB$64,"Amber")/COUNTIF(AA$14:AA$64,"H")<=0.5,COUNTIFS(AA$14:AA$64,"M",AB$14:AB$64,"Red")/COUNTIF(AA$14:AA$64,"M")>=0.7),FALSE))

For Amber fill:

Formula:

=OR(COUNTIFS(AA$14:AA$64,"H",AB$14:AB$64,"Amber")>0,COUNTIFS(AA$14:AA$64,"H",AB$14:AB$64,"Red")>0,IFERROR(AND(COUNTIFS(AA$14:AA$64,"M",AB$14:AB$64,"Amber")>=3,COUNTIFS(AA$14:AA$64,"M",AB$14:AB$64,"Amber")/COUNTIF(AA$14:AA$64,"M")>=0.5),FALSE),IFERROR(AND(COUNTIFS(AA$14:AA$64,"L",AB$14:AB$64,"Red")>=2,COUNTIFS(AA$14:AA$64,"L",AB$14:AB$64,"Red")/COUNTIF(AA$14:AA$64,"L")>=0.7),FALSE),IFERROR(AND(COUNTIFS(AA$14:AA$64,"M",AB$14:AB$64,"Amber")/COUNTIF(AA$14:AA$64,"M")<=0.5,COUNTIFS(AA$14:AA$64,"L",AB$14:AB$64,"Amber")/COUNTIF(AA$14:AA$64,"L")>=0.7),FALSE))

The cells have green fill applied as a default which will remain unless overridden by one of the other two rules.
Let us know if you have any questions.

I believe the following formulas will solve the issues from post #8.
The formula for the red rule should read:

Formula:

=OR(COUNTIFS(C$1:C$47,"H",D$1:D$47,"Red")>0,IFERROR(AND(COUNTIFS(C$1:C$47,"H",D$1:D$47,"Amber")>=2,COUNTIFS(C$1:C$47,"H",D$1:D$47,"Amber")/COUNTIF(C$1:C$47,"H")>=0.5),FALSE),IFERROR(AND(COUNTIFS(C$1:C$47,"M",D$1:D$47,"Red")>=3,COUNTIFS(C$1:C$47,"M",D$1:D$47,"Red")/COUNTIF(C$1:C$47,"M")>=0.7),FALSE),IFERROR(AND(COUNTIFS(C$1:C$47,"H",D$1:D$47,"Amber")/COUNTIF(C$1:C$47,"H")<=0.5,COUNTIFS(C$1:C$47,"M",D$1:D$47,"Amber")/COUNTIF(C$1:C$47,"M")>=0.7),FALSE))

The formula for the amber rule should read:

Formula:

=OR(COUNTIFS(C$1:C$47,"H",D$1:D$47,"Amber")>0,COUNTIFS(C$1:C$47,"M",D$1:D$47,"Red")>0,IFERROR(AND(COUNTIFS(C$1:C$47,"M",D$1:D$47,"Amber")>=3,COUNTIFS(C$1:C$47,"M",D$1:D$47,"Amber")/COUNTIF(C$1:C$47,"M")>=0.5),FALSE),IFERROR(AND(COUNTIFS(C$1:C$47,"L",D$1:D$47,"Red")>=2,COUNTIFS(C$1:C$47,"L",D$1:D$47,"Red")/COUNTIF(C$1:C$47,"L")>=0.7),FALSE),IFERROR(AND(COUNTIFS(C$1:C$47,"M",D$1:D$47,"Amber")/COUNTIF(C$1:C$47,"M")<=0.5,COUNTIFS(C$1:C$47,"L",D$1:D$47,"Amber")/COUNTIF(C$1:C$47,"L")>=0.7),FALSE))

On the attached file I included the formulas that govern rules #4 and #6, the ones that were listed as 'This does not work' in the 'Test' column, in cells G2:G3.
Let us know if you have any questions.

Re: post #10.
This is what I was talking about in post #2, the way the fourth rule is written causes the situation that is stated in the 'test results' column.
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
If less then 50% can mean 0% so even if no high critical stations are amber as long as 70% of the medium critical stations are amber it will result in RED
I would suggest that the rule be written that if there is at least one high critical station that is amber and if at least 70% of the medium stations are amber then it will result in RED.
In the attached file I changed the part of the RED conditional formatting formula that looks at the fourth listed rule so that it reads:

Formula:

...,IFERROR(AND(COUNTIFS(C$1:C$47,"H",D$1:D$47,"Amber")>=1,COUNTIFS(C$1:C$47,"M",D$1:D$47,"Amber")/COUNTIF(C$1:C$47,"M")>=0.7),FALSE)

Notice that the entire conditional formatting formula is tested in cell I2 and that the color of B3 is green as would be expected.
Re: Post #11 (I am going to wait and see if the proposed change above is acceptable before working on the issues noted in column N of the file attached)
Let us know if you have any questions.