Icon sets (up/down arrow) as values rise or fall

[gcefaloni]

Hi,

I have a single column of cummulative stock market returns going up and down on a daily basis. I'd like to have an up arrow show in the cell in question if the previous value in the column was lower and vice versa.
By cummulative stock returns, I mean if A1 shows the returns for January 2022, A2 would show the sum of January 2022 and February 2022. A3 would then take A2 (Jan+Feb) and add March, so on and so forth, to make a cummulative time series.
For the icon set questions. as an example: IF A1 = -2% and A2= -1% (which means Feb was +1%), I'd like A2 to show an up arrow even if the value is -1% because it's an increase compared to A1. If A3 was then -1.5%, that would be a down arrow because it's lower than A2.

What formula should I use in the conditional formatting fields using icon sets?

Thanks!

 

[portews]

The easiest way is to put this formula into A3. This will put an up or down arrow in the adjacent column.

=IF(A1<A2,UNICHAR(8593),(UNICHAR(8595)))

If you absolutely need the arrow in the same cell as this months percentage, you'll have to do the math in place of A2 and prepend that before the UNICHAR statements. I don't believe there's any way to suffix the value with the arrow in the same cell because that changes the value of the cell and becomes a circular reference. It makes my brain hurt.

 

[gcefaloni]

I had gone the way of conditional formatting - format all cells based on their values - format style (icon sets): up green arrow value > =OFFSET($O$4,ROW()-1,0,1,1), type=Formula. But that doesn't seem to work properly either.

I'd indeed need it to be in the same cell because I'm using it for presentation into a dashboard with multiple such logics and it would get quite messy otherwise (hidden columns and all).

 

[portews]

OK, I think I got this, try this...

First, make two separate Custom formats
##%↑;-##%↑
##%↓;-##%↓
You can make the up and down arrows by hitting the left ALT key and hitting 24 or 25 ON THE KEYPAD (not the numbers over the QWERTY keys)

Then, you'll need two different rules in the conditional format:

=$B1<$A1 with Stop if True checked
Set the custom text format of:
##%↑;-##%↑

and

=$B1>$A1
Set the custom text format of:
##%↓;-##%