Conditional formatting for one cell based on a Average from a Questionnaire

[nathanbrooks]

I am trying to get my conditional format setup correctly for a questionnaire. I want my auditors to go into a facility and plug in a score for each question. Once I do my sum to get my total score then get my average, I want to be able to color code that score. My questionnaire is 15 questions at 5 possible points each and the highest possible score being 75.

My example is below:

[TABLE="width: 202"]
<tbody>[TR]
[TD]90-100% to return Green
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]80-89% to Return Light Green
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]70-79% Yellow

60-69% Orange

59% and below red
[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[NiMip]

EDIT: Actually my apologies, I may have read the question wrong. Are you in fact wanting the score to be formatted based on the result of the average?

I would apply 5 individual rules to the cell holding the score. Choose the formatting rule "Format only cells that contain" and enter the following:

Cell Value | greater than or equal to | =90% | Formatted green
Cell Value | greater than or equal to | =80% | Formatted light green
Cell Value | greater than or equal to | =70% | Formatted yellow
Cell Value | greater than or equal to | =60% | Formatted orange
Cell Value | less than | =60% | Formatted red

As long as the rules appear in this order in the rules manager, they'll be preferenced in the way you're after (you might need to rearrange them, or they should be correct if you actually enter them in the reverse order listed)