Adding + before a positive percentage

[n18]

Hi,

Wondering why this is not working, I have data coming from a pivot, and what i need to do is, is show that if it is positive (+), basically if its 0.00 or above to put a '+' sign in front of it. I need to highlight it red also but I have this done using conditional formatting.

The issue is, my data needs to display as a percentage but as soon as I try and put a '+' in front of it, it wont display it as a percentage but displays it as a decimal. for example i currently have 32.22% as soon as i implement my formula it turns into + 0.322222222 In the formatting it still says its a percentage. My formula is

=IF('Overview Pivot'!C22>=0,"+ "&'Overview Pivot'!C22,'Overview Pivot'!C22)


What my deserved result is -32.22% or +32.22%

I need a + sign before the positive number that all's in theory, I have everything else working but just need to add this for clarity.

Any idea's?

Thanks

 

[Norie]

Why don't you try a custom format?

Something like this perhaps.

[Green]+#,##0.00%;[Red]-#,##0.00%;#,##0.00%

 

[VoG]

Try a Custom number format

+0.00%;-0.00%

 

[moonfish]

You can accomplish this with number formatting.

- Change the formula in the cell to ='Overview Pivot'!C22
- Right-click the cell, select Format Cells
- On the first tab, select Custom and enter the following format:

[Green]+0.00%;[Red]-0.00%;0.00%

 

[GlennUK]

Just use a custom number format, such as:

[Red]+#,##0.00%;[Green]-#,##0.00%

Excel Workbook
	A	B
1	100
2	32	+32.00%
3	-32	-32.00%
Sheet6

 

[n18]

Thanks guys, that sorted it, just found this, could be useful for someone else with the same problem

http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

Thanks