Replacing a value with symbol.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paperclip - 07 Mar 2006 13:45 GMT

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!:

Reply to this Message

Bob Phillips - 07 Mar 2006 14:18 GMT

Look at conditional formatting in help.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hi everyone,
>
[quoted text clipped - 15 lines]
>
> Mr Clip!:)

Reply to this Message

CarlosAntenna - 07 Mar 2006 14:57 GMT

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)))

--
Carlos

> Hi everyone,
>
[quoted text clipped - 15 lines]
>
> Mr Clip!:)

Reply to this Message

paperclip - 07 Mar 2006 15:23 GMT

geez you guys are clever here!!

Thanks. ;)

Signature

paperclip

Reply to this Message

random1970 - 08 Mar 2006 05:51 GMT

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.

Signature

random1970

Reply to this Message

George - 09 Mar 2006 16:35 GMT

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

> Hi everyone,
>
[quoted text clipped - 15 lines]
>
> Mr Clip!:)

Reply to this Message

paperclip - 20 Jul 2006 12:37 GMT

Belated thanks for the help guys!

George Wrote:

> Conditional formatting can be used to format the cell color, shadin
> etc.
[quoted text clipped - 54 lines]
> >
> > Mr Clip!:)

Reply to this Message