absolute values - conditional formatting

[capri]

Hi,

I have a table where I need to highlight the highest and lowest values(variances) in one colour, then the next largest variance in another colour.

I added a column to the table and put the following formula in =ABS(L5:L12).

In my conditional formatting I set three conditions
cell value is equal to =LARGE($N$5:$N$12,1) for condition 1
=LARGE($N$5:$N$12,2) for condition 2
and =LARGE($N$5:$N$12,3) for condition 3

condtiion 1 and 3 work properly but condition 2 is not working. The number happens to be a negative in column L but a positive in column N. I can understand that the values don't match and that is why the conditional formatting is not working.

Can anyone suggest how I could change the my syntax on the criteria to allow for negative numbers but still capture the 3 largest differences?

Thanks for any assistance

capri

[Rick Rothstein]

Don't use the "Cell is" option, use the "Formula is" option instead. Select your cells (N5:N12 I'm guessing) and note which one is the active cell (it will be the one that does not look shaded). Let's say the active cell is N5, then use these formulas for your three conditions...

Condition 1: =ABS(N5)=LARGE($N$5:$N$12,1)

Condition 2: =ABS(N5)=LARGE($N$5:$N$12,2)

Condition 3: =ABS(N5)=LARGE($N$5:$N$12,3)

[capri]

Thanks heaps.

Works fine. Never would have figured that one out on my own.

capri