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
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
