Leading + for positive value

[Andy Roberts]

How can I have excel show a posiitve value in a cell as +100 instead of 100.
Negatives arent a problem..

I'm trying to get a cell to display whether its value is higher or lower
than another value.

Andy

Excel 2007 / Win XP Pro SP3

 

[Niek Otten]

Format Custom as +#,00 or any other format.
The easiest way: first format as you wish it without the + sign, then Format
Custom and add the +

 

[Andy Roberts]

Thanks Nick

The problem is that this only seems to work if the value is positive. If
the value is negative it returns -+100

Andy

 

[RagDyeR]

Try this:

+General;-General
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thanks Nick

The problem is that this only seems to work if the value is positive. If
the value is negative it returns -+100

Andy

 

[Niek Otten]

Yes, You're right, I hurried too much.
Format as
+#,##0;-#,##0;0
The first item is the format for positive values, the second (after the
semicolon) for negative and the third for zero.
You can even add a 4th for text.

 

[Andy Roberts]

Just the job!

I've amended it slightly to =£0.00;-£0.00

Thanks

 

[Andy Roberts]

Thanks Nick

I didn't realise you could control it differently if the value was 0.

What would I need to do to set the cell to display nothing if the cell value
was 0?

Andy

 

[Niek Otten]

Just leave the last part out!
For example
+#,##0;-#,##0

Or RagDyeR's suggestion:

+General;-General

 

[Andy Roberts]

Nick

I currently have +£0.00;-£0.00;£0.00

If I change this to +£0.00;-£0.00 I get a result of +£0.00 if the value is
0. I just want the cell to be blank.

Andy

 

[RagDyeR]

Just *leave in* that last semi-colon, to denote the zero format:

+£0.00;-£0.00;

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Nick

I currently have +£0.00;-£0.00;£0.00

If I change this to +£0.00;-£0.00 I get a result of +£0.00 if the value is
0. I just want the cell to be blank.

Andy

 

[helene and gabor]

=if(a1>0, concatenate("+",a1),concatenate("-",A1))
Gabor

 

[helene and gabor]

hello, did you get my reply?
(e-mail address removed)

 

[Andy Roberts]

Helene

I've run with +£0.00;-£0.00;

see previous posts in same thread.

Thanks for your help


hello, did you get my reply?
(e-mail address removed)