Excel Streetlight color code

[Sergio]

Hello,
Quick and simple question. I have a questionnaire with many categories/sections and each category have one, ten or more questions in it.
The answers are yes or no and they have to put an X on the corresponding column

what is the formula to count
if all of the question yes = green
70% to 80% of the questions yes = yellow and
below 70% of the questions yes= red

Thanks
Alex

 

[Joe4]

So, you would have three Conditional Formatting Rules/formulas, one for each condition.

Formula for Green Rule (assuming range F1:F10):

=(COUNTIF(F1:F10,"yes")/COUNTA(F1:F10))=1

Formula for Yellow Rule (assuming range F1:F10):

=AND(COUNTIF(F1:F10,"yes")/COUNTA(F1:F10)>=0.7,COUNTIF(F1:F10,"yes")/COUNTA(F1:F10)<=0.8)

Formula for Red Rule (assuming range F1:F10):

=(COUNTIF(F1:F10,"yes")/COUNTA(F1:F10))<0.7

But what is supposed to happen if it is between 80% and 100%?

 

[Sergio]

So, you would have three Conditional Formatting Rules/formulas, one for each condition.

Formula for Green Rule (assuming range F1:F10):

=(COUNTIF(F1:F10,"yes")/COUNTA(F1:F10))=1

Formula for Yellow Rule (assuming range F1:F10):

=AND(COUNTIF(F1:F10,"yes")/COUNTA(F1:F10)>=0.7,COUNTIF(F1:F10,"yes")/COUNTA(F1:F10)<=0.8)

Formula for Red Rule (assuming range F1:F10):

=(COUNTIF(F1:F10,"yes")/COUNTA(F1:F10))<0.7

But what is supposed to happen if it is between 80% and 100%?

good question i think it will be yellow.... it can not be green if is not 100% completed.

 

[Joe4]

good question i think it will be yellow.... it can not be green if is not 100% completed.

If you want it yellow, then just change the yellow formula to:

=AND(COUNTIF(F1:F10,"yes")/COUNTA(F1:F10)>=0.7,COUNTIF(F1:F10,"yes")/COUNTA(F1:F10)<1)