Conditional Formatting Analysis of Rates (20% above and 20% below the average)

**barleyhill** · 03-12-2013, 02:43 PM

Hi,

Forgive, this seems very basic.

I have 4 companies who have priced the same items. I now have to do a rate analysis.

I am trying to conditional format;
any rates 20% above the average 'red'
any rates 20% below the average 'green'

Cheers for any help on this.

**etaf** · 03-12-2013, 03:57 PM

in the conditional format
=IF($O8*0.8>F8,TRUE,FALSE) = green, if the value of the company is below the average-20%
=IF($O8*1.2<F8,TRUE,FALSE) = red if the value of the company is above the average+20%

**Pepe Le Mokko** · 03-12-2013, 04:02 PM

@etaf ALthough your solution is correct, the use of IF, TRUE and FALSE is not necessary for CF.

=$O8*0.8>F8

will return TRUE or FALSE implicitly to trigger the format

**etaf** · 03-12-2013, 04:20 PM

Pepe Le Mokko thanks for pointing that out

its a habit , i seem to have formed , as i test in a cell the cond format on more complex tests using ANDs/ORs and different cells - and so do for all now

i'll try and drop on future posts

thanks again