Formula using arrow icons to show trend

**casdaq** · 11-16-2009, 07:04 PM

I'm trying to create a formula where I can show the trend of two values from one month to the next that shows wheather the results went up or down in a positive or negetive value. For example:

If I have a transfer % of 18% in October and November MTD is at 17% - the arrow would then reflect downward showing in green, as the lower percentage is better. but I need the arrow to show upward in Red if say the value for November MTD was 19% instead.

I tried conditional formatting and attempted an "If" formula. I must not have all the information to create the formula as it errors out on me. I can create it if only looking to reflect one arrow...

=if(c3<b3,"↓")

Any help is greatly appreciated.

FYI - I have excel 2007.

**teylyn** · 11-16-2009, 07:16 PM

You need a combination of two things:

first, a formula that will put the arrow in the cell.

=if(c3<b3,char(234))
format the cell as wingdings

Then, conditional formatting to format the font color.

see attached

**casdaq** · 11-16-2009, 07:36 PM

Thank you for the reply. I didn't see anything attached but tried what you mentioned and it didn't seem to work for me. If there is a way to add to my "If" function to show both arrows I think that may be what I'm looking for. Something like this although I know this isnt right...

=If(c3<b3,"↓")&if(c3>b3,↑)

**martindwilson** · 11-16-2009, 07:37 PM

you might want to change to martlett font
and use say
=IF(C3<B3,"t",IF(C3>B3,"u","v"))
this will give
t gives up triangle
u down triangle
v up and down triangle indicating no change

**teylyn** · 11-16-2009, 07:47 PM

sorry, must have forgotten to click the upload button. I added the file above.

**Paul** · 11-16-2009, 07:53 PM

In Excel 2007, you can use conditional formatting with icon sets to show arrows for trends (up, down, stable, etc).

**casdaq** · 11-16-2009, 07:57 PM

Just wanted to thank everyone for their help. I found an old formula I had that worked for this and figured out how best to use it with the conditional formatting.

Thanks again!

**flowerflower** · 11-30-2012, 05:04 PM

This thread is really helpful! However if I want to include the ranking difference from the last period to show like "up 5", is this possible by using one formula? Also I've tried to combine the arrows with the absolute numbers (by using CONCATENATE formula) but it seems really tricky to combine the winddings font with other formats... I've been pulling off my hair because of this for the whole afternoon!

Thank you gurus!!

-Iris