The built-in capability of icon sets display the appropriate icon only when the cell value meets some criterion. One cannot condition the icon on data in another set. So, how then can one use the c.f. icons to plot a trend? Consider the data set I created for someone asking for help in an Internet forum. What he or she wanted was up / down / sideways arrows depending on whether the value in a cell was higher / lower / the same as the value in the cell above it.
In this case, we need some way to flag the direction of the change on a cell-by-cell basis. Of course, we cannot create a c.f. formula based solution for the icon. So, the question becomes how do we create a value in a cell that reflects the direction of the change in value?
The easiest way would be to put this information in an adjacent column, i.e., column B, and create the icon set in column B. Though the icon will be in column B it will be flush with column A. It will look like the icon is to the right of the number in A rather than to the left. But other than the position of the icon we will have solved the problem. And, of course, if the position of the icon is important, move the data from column A to column B and put the direction information and the icon in column A!
In B2 enter the formula =SIGN(A2-A1). Next, copy it as far down as there is data in column A. The quickest way to do this is to move the cursor to the bottom right corner of B2 until the cursor becomes a thick +. Double click and Excel will copy the formula down the correct number of rows. Now, column B will be +1 if the value in the adjacent cell increased from the value above it, -1 if it decreased, and 0 if it remained the same.
We can now specify an icon set for column B based on the value in column B itself!
The result will be
