Replacing a value with symbol.

[paperclip]

Hi everyone,

I wondered if there was a way to replace a value generated by a formul
in a cell with a symbol or an image, or even to automatically change th
color of the value in the cell.

To give you a better idea:
I want to calculate the change between to sets of values as
percentage and then replace the actual value eg. 10% with a symbol t
denote a positive or negative change (maybe an arrows up and down).

Firstly, is this at all possible?

Any help with this would be much appreciated, or if you have a bette
idea on how I could present this I would love to hear it too.

Regards,

Mr Clip!:

 

[Bob Phillips]

Look at conditional formatting in help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[CarlosAntenna]

This is how I did it:

If your value is in A1 put this formula in B1 and format font to Wingdings
3.

=IF(A1>0,CHAR(199),IF(A1<0,CHAR(200),CHAR(198)))

 

[paperclip]

geez you guys are clever here!!

Thanks.

 

[random1970]

Paperclip,

Bob mentioned conditional formatting, which is the way to go. Carlos'
formula gave you the symbol you want, now just conditionally format the
cells to give you the colour you want.

Select one of the cells with the symbols in it. Go to the Format Menu,
select the Conditional formatting option. In the window that appears,
click the down arrow next to Cell Value is, and select Formula is. In
the formula bar, type =A2<0 (where A2 is the cell you want to check
whether it is positive or negative). Click the Format button, and
select the text colour you desire (red in this case). Then add another
condition, this time being =A2>0 and again select the colour you
require. Ensure that absolute value references are not present in the
formulas. Then once you have that cell formatted correctly, use the
format painter to copy this to other cells.

Good Luck.

 

[George]

Conditional formatting can be used to format the cell color, shading etc.
But to change the displayed text you can use the cell number format
Format > Cells > Number then select Custom
Use semi-colons to separate the sections
In the type box you can type
"UP";[RED]"DOWN";"-"

First section is for positives
Second section is for negatives
Third section is for zero
and a fourth section for text (if required)

You can replace the words up and down with something else
I personally use an up triangle and a down triangle instead of the words

This is a bit tricky ... but read on if you like
Goto any empty cell
Goto insert > symbol
Under normal text , subset: geometric shapes
Insert both the down and up triangles and then close the dialog box.
Then edit the cell, highlight both triangles and press ctrl-c to copy
Then go back to the cell you formatted above
Format > Cells > Number then select Custom (like before)
Except this time highlight the word UP in between the quotes
and press ctrl-v, then delete just the down triangle.
Do the same for the word DOWN, highlight it and press ctrl-v
This time delete the up triangle.
Thats it.
Oh, you can now clear the cell with the 2 triangle symbols


Good luck with it
George

 

[paperclip]

Belated thanks for the help guys!

Conditional formatting can be used to format the cell color, shadin
etc.
But to change the displayed text you can use the cell number format
Format > Cells > Number then select Custom
Use semi-colons to separate the sections
In the type box you can type
"UP";[RED]"DOWN";"-"

First section is for positives
Second section is for negatives
Third section is for zero
and a fourth section for text (if required)

You can replace the words up and down with something else
I personally use an up triangle and a down triangle instead of th
words

This is a bit tricky ... but read on if you like
Goto any empty cell
Goto insert > symbol
Under normal text , subset: geometric shapes
Insert both the down and up triangles and then close the dialog box.
Then edit the cell, highlight both triangles and press ctrl-c to copy
Then go back to the cell you formatted above
Format > Cells > Number then select Custom (like before)
Except this time highlight the word UP in between the quotes
and press ctrl-v, then delete just the down triangle.
Do the same for the word DOWN, highlight it and press ctrl-v
This time delete the up triangle.
Thats it.
Oh, you can now clear the cell with the 2 triangle symbols


Good luck with it
George

Hi everyone,

I wondered if there was a way to replace a value generated by formula
in a cell with a symbol or an image, or even to automatically chang the
color of the value in the cell.

To give you a better idea:
I want to calculate the change between to sets of values as a
percentage and then replace the actual value eg. 10% with a symbo to
denote a positive or negative change (maybe an arrows up and down).

Firstly, is this at all possible?

Any help with this would be much appreciated, or if you have better
idea on how I could present this I would love to hear it too.

Regards,

Mr Clip!