impossible conditional formatting? complex RAG status

Any professor types out there that are up for an impossible challenge?

I need to RAG overall section 1 (row 7) based on the completeness of the subsections 1.1 to 1.6.

So,

section 1 would turn red if 1.1 to 1.6 have anything in the "no" box

section 1 would turn red/amber if two of 1.1 to 1.6 have anything in the "yes" box

section 1 would turn amber if three or four of 1.1 to 1.6 have anything in the "yes" box

section 1 would turn amber/green if five of 1.1 to 1.6 have anything in the "yes" box

section 1 would turn green if all 1.1 to 1.6 have anything in the "yes" box

The doozy - if the N/A is ticked, this row needs taken out of the equation or classed as a "yes" for the purposes of RAG


impossible example.png

Thanks! This is amazing!

I will probably end up using this as I'm not sure if the following challenge makes it totally impossible??

After having a play with it - is there a way to exclude the N/A's altogether? So if there is only 5 deliverables instead of 6, it works out the RAG on a percentage basis? Kind of like putting an N/A in tells the conditional formatting not to affect the RAG and the section cell only RAG's on the number of Yes / No's there?

so something like

100% Yes = Green
75% - 99% Yes = Green / Amber
50% - 74% Yes = Amber
25% - 49% Yes = Amber / Red
0% - 25% Yes = Red

Create 1st conditional formatting using this formula with Red color:

Formula:

Please Login or Register  to view this content.

Create 2nd conditional formatting using this formula with Red/Amber color:

Formula:

Please Login or Register  to view this content.

Create 3rd conditional formatting using this formula with Amber color:

Formula:

Please Login or Register  to view this content.

Create 4th conditional formatting using this formula Amber/Green color:

Formula:

Please Login or Register  to view this content.

Create 5th conditional formatting using this formula with Green color:

Formula:

Please Login or Register  to view this content.

See the attachment. I am not familiar with your color. So I have used different color.

Perfect! That's nailed it! Thanks for your help PFDave

Hi PFDave - got another little dynamic to add in and cant seem to work it. Hoping you can help?

In short, added an "on track" column - if this has a Yes in it, want it to count as a Yes for the Section RAG but turn the individual deliverable yellow (if that makes sense?)

(Think I have reattached the xls?)

For "Section" rules


=IF(SUM((COUNTIF($F$8:$F$13,"<>"&"")+COUNTIF($H$8:$H$13,"<>"&""))/SUM(COUNTIF($F$8:$F$13,"<>"&"")+COUNTIF($G$8:$G$13,"<>"&"")+COUNTIF($H$8:$H$13,"<>"&"")))>0.99,TRUE,FALSE)

Change each one with appropriate value

For "Deliverable"

=$H8="Yes"

Fill=Yellow

Applies to: $H$8:$H$13

I cant seem to make this work - any chance someone can pop it into the template?