Down arrow Negative number

[MikeyG498]

I am creating a budget type for in 2007 and i was wondering if I could get a down arrow to appear if the number is negative or up arrow if the number is positive? Thanks in advanced.

 

[mileskb]

Try =IF(D4>0,A1,IF(D4<0,A2,A3))

In cell A1, insert a symbol for Up Arrow, A2, a symbol for down arrow, A3, <----> arrow (if you want it for 0).

D4 would be the positive/negative number of interest

 

[wsjackman]

Will this work?
format column B as wingdings

Excel Workbook
	A	B
1	1
2	-1
3	0
Sheet1

 

[MikeyG498]

Is there anyway I can put the arrow in "D4" with the number.

Lets say I have this
D1 = "$230"
D2 = "$300"
D4 = "=D1-D2"

If the number is D4 is negative can I have the arrow appear in D4 with the number?

 

[mileskb]

Yes, in D4, put:

=D1-D2&" "&IF(D4>0,A1,IF(D4<0,A2,A3))

That should work

 

[mileskb]

Sorry, replace D4 with D2-D1 in the code above, since D4 would create a circular reference.

 

[MikeyG498]

It didn't work.

 

[mileskb]

=D2-D1&" "&IF(D2-D1>0,"↑",IF(D2-D1<0,"↓","↔"))

I just plugged this into Excel 2003 and it worked. I took out the A1, A2, A3 and put the symbols right into the formula. Copy and paste this and it should work for you.....

 

[MikeyG498]

I figured it out. In Excel 2007 if you go to conditional formatting and setup rules you can do it that way. Thanks for all your help though.

 

[wsjackman]

you can also do it with custom formating

#,##0"­";-#,##0"¯";#,##0__
entered in the format cells-custom

and format the cell font as symbol
note the

"­" actually has a character in the quote marks which when font is symbol is the up arrow
and "¯" actually contains the down arrow in symbol font
take =char(173) and =char(175) which in symbol are and down arrows
copy and paste special values copy each symbol whihc appears and paste into the custom number format.

I haven't been able to format it as $ when in symbo. someone else may be able to.

the nice portion of this is that it is still a number.

the Ecel 2007 condional formatting has a problem In that I haven't been able to have a nothing when the cell has zero.