Conditional Formatting with Formula - have I got the order of precedence wrong?

Hi,

I have set up the attached sample data to test my understanding of conditional formatting (CF).

The required cell fill colours are shown in column A, the associated CF conditions in column B and the values to be thus formatted in column C.

I seem to have set up the correct formulae for the CF but all results in column C are formatted as if they're <-0.50, which only one is.

Can someone please tell me what I'm doing wrong?

Thanks!

Red =$C2<-0.5
Amber =AND($C2>=-0.5,$C2<=0.5)
Green =AND($C2>0.5,$C2<>"n/a")
---------------
=$C$2:$C$5>0.5 is only looking at the first cell in the range but applying that condition to every cell in the range.

As the others have said, you just need to refer to the top-left cell of the range you're trying to format. Excel will apply the formula as you enter it to this top-left cell. It will then, in the background, alter the row and/or column it is looking at to the next cell down / across, unless you lock the reference by using the $ sign.

So, let's say you want to CF the range C2:D4. You put in the formula =C2<0.5 - with red fill. Excel looks at C2 and checks if it is <0.5 - if so it fills C2 red. Then it looks at C3 and uses the formula =C3<0.5 (you don't see this, it happens in the background) and so on until it gets to D4 where it uses =D4<0.5 and formats appropriately.

If you were to use =$C2<0.5 then when it moves on to column D it will still look at column C (so using =C2<0.5 for cell D2). Similarly if you used =C$2<0.5 it would always look at row 2 (so using =C2<0.5 for cell C4). If you use $ signs for both, it will always look at the figure in that single cell to format the entire range.

Hope that makes sense, sorry for the long post.

Glad we could help and thanks for the rep.