Comparing Mean against Overall Mean using Conditional Formatting

[MdotM]

Hi,

I'm currently working on a project where I have a number of statements which are rated between 1(Strongly Disagree) and 4 (Strongly Agree). For each organisation I am presenting their mean average on this statement against the combined mean average for all other organisations, and I would like to create a traffic light code to indicate whether the organisation is on track (Green), improvements could be made (Amber) or this area needs to be focussed on (Red).

The mean average for each organisation and mean average for all organisations are presented in cells next to each other, but are pulled from data from another worksheet in the document, so both of these cells already contain formulas.

I would like the mean average cell (X) to contain a formula that covers these criteria in relation to the overall combined average cell (Y):

If Y is 2.25 or more
-If X is 0.25 or more than Y, X is Green
-If X is less than +/- 0.25 than Y, X is Amber
-If X is less than 0.25 than Y, X is Red

If Y is 2.24 or less
-If X is 0.25 or more than Y, Amber
-If X is less than +/- 0.25 than Y, X is Amber
-If X is less than 0.25 than Y, X is Red

Is this formula possible to build using conditional formatting within a single cell, and if so what would the formula be? Also as the cells containing the means already contain formulas to pull the data from another worksheet in the excel doc, is it still possible to build the conditional formatting into these cells?

Thanks for your help with this in advance

 

[DRSteele]

Welcome to the forum.

Please look at this grid and let us know if the colours are correct.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	G	H	I	J	K	L	M	N
1	0.25
2	y	x
3	3	3.26	3.25	3.24	3.00	2.76	2.75	2.74
4	2.26	2.52	2.51	2.50	2.26	2.02	2.01	2.00
5	2.25	2.51	2.50	2.49	2.25	2.01	2.00	1.99
6	2.24	2.50	2.49	2.48	2.24	2.00	1.99	1.98
7	2	2.26	2.25	2.24	2.00	1.76	1.75	1.74
Sheet45

[/FONT]

 

[MdotM]

Hi,

Thanks for your help with this, yes the colours are correct in this grid. How can I build this into my report?

Many thanks,