Using Conditional Formatting with Icon Sets to Compare Cell Value Differences

Question

I’ll give an example to hopefully make this faster.

In Column "Tear Height" or column AJ, row 161, I have a value of 200. Going down the table (each row is a new bond for what I’m doing, so a parameter is going to be changed), the value of 200 increases by 10 each cell (AJ162, cell value = 210, etc.), until AJ166. From AJ166 to AJ169 the cell value stays at 250. The value then goes up again by 10 in AJ170. In AJ171, the value drops to 240, and continues to drop until AJ175.

What I would like, is to have Conditional Formatting Icon arrow sets, that show if a value increases, decreases, or stays the same, from the cell directly above it, rather than being given a specific number. Similar with stock market value changes, if the value goes down from the last time it was checked, a red down arrow. If it goes up, a green up arrow. For me, if the value doesn't change, then the yellow dash.

I would submit an image so that it is easier to understand, unfortunately I need more reputation.

I hope that you are able to help me out with my question Many thanks!

Answer 1

Conditional formatting rules with icon sets cannot use a formula with relative cell references so they cannot be created on a range of cells that look to the value immediately above themselves to distinguish which icon to display. However, if you use a 'helper' column with a formula that determines the trend from the last cell, you can apply the icon set to that and optionally use the Show icon only option.

    

The formula in AK162 is,

=SIGN($AJ162-$AJ161)

Fill down as necessary. The SIGN() function isn't absolutely necessary as the difference between the two relative values being positive, negative or zero is the determining factor.