Hi.
Excel Icon sets specifically exclude the ability to use relative references in formulas. However, you can get around this in a number of ways :
1. Use the INDIRECT function in your formula.
2. Create a single Conditional Format rule for the first cell in your range (using an absolute Cell Reference) - and then COPY the FORMAT down to the other cells.
(in other words each cell has it's own conditional format referring to the cell above, rather than one conditional format that "applies to" a range of cells.
3. Create a little "helper column" next to the values with the desired result of your formula and put the conditional format on THAT column.
The steps below illustrate
technique 3.
[TABLE="width: 118"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]171[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]166[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]166[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]161[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]163[/TD]
[TD]-2[/TD]
[/TR]
</tbody>[/TABLE]
.. where the formula in Column B2:B5 is "=A1-A2".
Then put an Icon Set on
Column B eg as follows :
Format Style : Icon Sets
Icon Style : 3 Symbols (circled)
When value >= 1 Number Green Icon
When < 1 and >= 0 Number Orange/Yellow Icon
When <0 Red Icon
Tick the "Show Icon Only" box.
An image of the basic setup and result is here at
https://pasteboard.co/HbDGiZW.jpg
If you are using Excel 2010 and above you can also mix and match Icons from Multiple sets, using eg The Green Tick and Red "x" from the
3 Symbols circled set, and taking eg the Orange Dash from the
Three Triangles Icon set instead of the Yellow "!" in the 3 symbols circled set.
Cheers,
Warren K.
