Inserting symbols using =IF?

[reducedjuice]

Quick question in Excel07:

I am writing an if statement to compare the values of two cells (both numeric values); call them X and Y to keep it simple.

If Y>X, I want there to be a nice green arrow displayed next to Y to indicate that the value is greater than X.


Can this be done using an IF statement? =if(Y>X,InsertGreenArrow,InsertRedArrow)

Kind of a left-field question

Thanks!
-reducedjuice

 

[shyrath]

Sure there is

you need conditional formatting for the green and red.
you need to set the font as wingdings (or similar)
get the letter reference for the up and downarrow , I thought it was J for up and K for down, but that´s easily checked in word or wherever.

If you put the formula

=IF(Y>X,J,K)

And do conditional formatting on that cell, e.g. paint font Red if K and paint font green if J

 

[Okatoks]

I can not think of a function to do this, but I'm pretty sure it could be done with code. Here's perhaps an easier solution.

Place an Autoshape arrow in the desired cell. Make the arrow's fill the same color as the cell background, and then turn off the arrow's outline. At this point, you will not be able to see the arrow.

Now select Format > Conditional Formatting, and conditionally format the color of the cell to be green when Y>X, and red when X>Y. When a condition is met, the cell color changes to either green or red, and you can see the arrow.

 

[shyrath]

On the Dutch Layout it would look like this:

Excel Workbook
	B	C	D
4	Y	X	Up/Down
5	3	2
6	1	2
7	3	2
8	1	2
9	3	2
10	1	2
11	3	2
Sheet1

 

[Peter_SSs]

reducedjuice

Since you are using Excel 2007, you could also use some of the new built-in features to achieve this. You didn't say what you wanted if X=Y, but see if this helps.

1. Try a formula like this in C2 (copied down)

Excel Workbook
	A	B	C
1	X	Y
2	8	4	-1
3	2	2	0
4	1	3	1
5	-5	3	1
6	200	4	-1
Sheet1

2. Select C2:C6

3. Home tab|Conditional Formatting|Icon sets|choose the first one with green up arrow, orange sideways arrow and red down arrow.

4. (Optional) Home tab|Conditional Formatting|Manage rules...|select the icon set rule|Edit Rule...|Show icon only (bottom right)|OK|Apply|OK