Conditional Formatting by Row

[neville]

Hi All,

This is an area of excel I do not use that much.

I have country by rows (226 of them) Columns are months Jan to Dec. I have data in months Jan to May with an average at the end of the row for each country.

I need a RAG status for that country based on each countries average.

I need to conditionally format each row - Greater than average and less or equal to than average. But I keep getting referencing ($) per line, when I change the reference - like dropping the $ off the row number - it puts it back after I apply the rule.

Can anyone advise me please.

Rgs

Neville

 

[Andrew Fergus]

Hi Neville

Using this set-up:

  A  B  C  D  E  F  G  H  I  J  K  L  M  N    
1    J  F  M  A  M  J  J  A  S  O  N  D  Avg  
2 1  10 15 18 16 14                      14.6 
3 2  3  5  7  6  1                       4.4  
4 3  5  5  5  6  6                       5.4  
5 4  12 13 13 14 9                       12.2 
6 5  20 20 20 20 20                      20   
Sheet2
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
N2:N6 =AVERAGE(B2:M2)
[Table-It] version 09 by Erik Van Geit

I set up the conditional formatting as follows:

Select range B2:M2 > select menu option Format > Conditional Formatting >

Condition 1 : Cell Value is : equal to : 0 : no format
Condition 2 : Cell Value is : less than : =$N2 : orange colour
Condition 3 : Cell Value is : equal to : =$N2 : green colour

> Ok. Copy the range B2:M2 and paste special (formats) over the rest of the range. Whilst you can't see colours in my example, it has coloured the low values orange and the high values green. Zeroes and values equal to the average have no format.

Can you get this to work?

Andrew

 

[neville]

Thanks for that I will give it a try and let you know if I screwed up.

Rgs

Neville