Displaying a + sign on positive numbers

[Alex]

I am calculating the percentage increase / decrease on two numbers. If the
difference results in a decrease of, say 2.2% it is displayed as -2.2%

If the result is an increase of 2.2%, then 2.2% is displayed. Purely for
formatting reasons, I wish to display the figure as +2.2%

How can I set this up so that positive numbers contain a plus sign, whilst
maintaining the cell as a number? I can carry out an IF statement that will
add a "+" to the figure if it's greater than zero, but this ignores any
formatting to a set number of decimal places.

Any help will be appreciated!

 

[David]

Select the cells of interest, then select Format | Cells and select Custom
in the Category list.

In type click on the line that shows:

# ##0;-# ##0

And this will apear up above in the Type box.

Simply edit to put a + in front of the first part, so it now reads:
+# ##0;-# ##0

and that should do the trick.

If you want slightly different formating, edit accordingly. The part before
the ; is for +ve numbers, that after the ; is for negative numbers.

HTH

David

 

[Ron Rosenfeld]

I am calculating the percentage increase / decrease on two numbers. If the
difference results in a decrease of, say 2.2% it is displayed as -2.2%

If the result is an increase of 2.2%, then 2.2% is displayed. Purely for
formatting reasons, I wish to display the figure as +2.2%

How can I set this up so that positive numbers contain a plus sign, whilst
maintaining the cell as a number? I can carry out an IF statement that will
add a "+" to the figure if it's greater than zero, but this ignores any
formatting to a set number of decimal places.

Any help will be appreciated!

Format/Cells/Number/Custom/Type: +0.0%;-0.0%;0.0%


--ron

 

[Ture Magnusson]

Alex,

Use a custom number format, like this:

1. Select the cells with percentages
2. Format - Cells
3. On the "Number" tab, select "Custom" category
4. Enter this in the "Type" text box:

+0.0%;-0.0%;0.0%

(This sets how positive, negative and zero values are
displayed. The three number formats are separated
by semicolons.)

5. Click "OK"