trend arrows

[nparsons75]

Hi,

I have a table that contains data for a 12 month period. Month on month per cell I would like to display trend arrows showing the trend against the previous month.

For example, column A contains January, B February and so on. How can I show a trend arrow in B against the value in A. Then again in C for the value in B. Basically showing the trend against the previous month for all 12 months.

Thanks in advance,

 

[MickG]

Perhaps this (or similar) with cell orientation moved through 90degees:-

=IF(B4>A4,"->","<-")

 

[63falcondude]

How about something like this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17
2	1	2	3	3	2	1	2	3	4	1	1	2
3		?	?	?	?	?	?	?	?	?	?	?
Sheet1

Cell Formulas
Range	Formula
B3	=IF(B2>A2,"⇧",IF(B2))

The question marks will appear as the arrows in the formulas.

 

[nparsons75]

Hi, great thank you. We'll give them a go and feedback. Is it not possible to use conditional formatting?

 

[63falcondude]

Hi, great thank you. We'll give them a go and feedback. Is it not possible to use conditional formatting?

It depends on what it is that you are looking to do.

If you want to change the fill or font color if they go up or down (or stay the same) then you can do this with Conditional Formatting.

However, if you are trying to put arrows in the cells through icon sets, I do not believe that you can use Conditional Formatting for this since icon sets cannot use relative references.

Edit: There might be a loophole by using INDIRECT. I will let you know if I find anything more on this.

 

[nparsons75]

That would be brilliant, thank you. Im about to try the other options also.

 

[63falcondude]

That would be brilliant, thank you. Im about to try the other options also.

The best that you can do through Conditional Formatting (in my opinion) would be to show icon sets directly below the numbers.

So in the example shown in post #3, you can create an icon set showing in row 3.

To do this, given the layout in post #3, put the formula =SIGN(B2-A2) in cell B3 and then drag the formula through cell L3.

This will give you 1 when the number increases, -1 when the number decreases, and 0 when the number stays the same.

Now highlight B3:L3 > Conditional Formatting > Icon Sets > 3 Triangles (assuming Excel 2007+)

Then with B3:L3 still highlighted, go to Conditional Formatting > Manage Rules > Edit Rule > Check the box labeled "Show Icon Only" > OK > OK

You can then center everything to make them look nice. Hope this will work for you.