Conditional formatting in Excel 2007

[MNN]

I am trying to use conditional formattingas follows:
if a % is between 0 and + or - 8.99% record a green dot icon;
if the % is between 9% and 19.99% or -9% and -19.99% record a yellow dot;
if the % is greater than 20% or less than -20.0% record a red dot;

[MNN]

Sorry, I pulled the trigger too fast. to the above I am attaching a worksheet to demonstrate.

The new conditional formatting looks awesome.

Thank you

[BerylM]

LOL - well, I can do the conditional formatting easily enough - but what I can't figure out is how to keep the cells being referred to as a relative link instead of an absolute one! That is, I write the formulae for the first cell, then before you used to just copy it to the rest and the cells referred to would update along with it, same as ordinary cells do. Eg "E2=1" in the first cell, copied down one cell would become "E4=1" - but now XL just makes it absolute and refers to a range to apply the formatting to instead.

I presume the answer is relative cell addressing (R1C-1 or similar) but I don't know and haven't been able to work out how to use this ...

Edited: PS I'm presuming here that what MNN wants is to colour the words 'Green', 'Yellow' and 'Red' in his sample spreadsheet, according to his formulae - he can then change the text to a dot as required ...

[sdckapr]

Use <F4> to toggle through the cells to change from absolute to relative ($A$1, $A1,A$1,A1). The conditional formatting uses those references in the cells. The formula references are all based on the activecell of the selected range.

Steve

[rory]

If you add a formula to that column using ABS to convert the absolute percentage values, you can then use icon sets and hide the formulas. See attached.

[MNN]

Rory came up with a winner. The formula making the variance % an absolute is key. However, What happens to the result? Only the conditional formatting icon is shown. I thought both the conditional formatting icon and the formula result would be demonstrated. Why does this happen in this conditional formatting situation.

Thanks again.

[rory]

I specifically hid the result using a cell format of ;;;
If you want to show it, simply reset the cell format to Percentage.

[MNN]

Neat trick. Thank you again.