Colour coding results

[leewisener]

I want to change the colour of a cell based on a result, my basic formula is:

+(B9/(B7+B8)*100)

This returns a % value of work completed, I want then to make that green if the result is above 80%, orange if between 50%-79% and red if below 50%

Is that possible?

Thanks

Lee

 

[theo1881]

conditional formatting in the cell itself, can add a number of conditions for the cell to meet and change font colour etc !

Laters, phat, headshape

 

[StuckInTheMiddle]

Yes, Theo is right, conditional formatting is the way to go. Simply select the column (or columns) you wish to have formatted this way and then from the file menu choose Format>Conditional Format and enter in the 3 conditions you have. The maximum allowed is 3 so you lucked out!

A,

 

[Tonyjstone]

Hi Lee,
I don't know how many colour formats you need, but this is a procedure I developed some months ago and it works in the circumstances I have on one application. It may do for you.


If you use Excel Conditional Formats & Excel Custom Formats you can have up to 6 Conditional Formats for Font colour.

In the following example up to 6 colours can be invoked.
If value less or equal to 0, then make font Red.
If value greater than 0 but less than or equal to 20, then make font Green.
If value greater than 20 but less than 31 , then make font Blue.
If value is between 31 and 40 make font Tan.
If value is between 41 and 50 make font Grey-50%.
If value is greater than, or equal to 51 make font Brown.

To achieve this result select the cells to be formatted and then go to Format>Format Cells - Number and use the Custom Format as listed below.

[Red][<=0]0;[Green][<=20]0;[Blue]0

Click Ok to return to Excel.
With the cells still selected go to Format>Conditional Formatting and set the 3 format criteria allowed.

I hope that helps
Tony