Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | (show all)   Print Thread
Standard User GeoffB
(fountain of knowledge) Fri 22-Feb-13 17:40:06
Print Post

Negative percentage cell in Excel 2010


[link to this post]
 
Is there any way of making a percentage format cell turn red if the percentage is negative? I can do this for some other number formats and I'm sure there must be a way for percentages too. No wisecrack comments about changing font colours, please! - I'd like it to be automatic as we're talking about 50 cells in a 400 cell column.

Dell Studio1558 with Win7 Home Premium 64bit 8GB RAM
IE9 and Live Mail
BT Infinity via HH3
Standard User Pipexer
(eat-sleep-adslguide) Fri 22-Feb-13 17:43:59
Print Post

Re: Negative percentage cell in Excel 2010


[re: GeoffB] [link to this post]
 
I presume you have tried conditional formatting??

Zen 8000 Pro
Standard User XRaySpeX
(eat-sleep-adslguide) Fri 22-Feb-13 18:09:07
Print Post

Re: Negative percentage cell in Excel 2010


[re: GeoffB] [link to this post]
 
##0.00%;[Red]-##0.00%

Omit the '-' if you don't want it displayed.

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC

Edited by XRaySpeX (Fri 22-Feb-13 18:09:53)


Register (or login) on our website and you will not see this ad.

Standard User camieabz
(sensei) Fri 22-Feb-13 18:09:58
Print Post

Re: Negative percentage cell in Excel 2010


[re: GeoffB] [link to this post]
 
As Pipexer says, use conditional formatting to format cells that are less than zero to suit.

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.
Standard User GeoffB
(fountain of knowledge) Fri 22-Feb-13 18:46:00
Print Post

Re: Negative percentage cell in Excel 2010


[re: Pipexer] [link to this post]
 
Ah, yes! I'd used this before to highlight certain cell values but hadn't realised that it was so powerful. Thanks everyone.

Dell Studio1558 with Win7 Home Premium 64bit 8GB RAM
IE9 and Live Mail
BT Infinity via HH3
Standard User XRaySpeX
(eat-sleep-adslguide) Fri 22-Feb-13 19:09:17
Print Post

Re: Negative percentage cell in Excel 2010


[re: GeoffB] [link to this post]
 
What I don't understand is:
If I put '1' in a %age cell it displays '1%' as you'd expect,
But if I put '-1' in a %age cell it displays '-100%' frown

Ah, it can't handle signed %ages!

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User camieabz
(sensei) Sat 23-Feb-13 00:58:24
Print Post

Re: Negative percentage cell in Excel 2010


[re: XRaySpeX] [link to this post]
 
Not me. I format a range of cells as % with 2 DPs and enter a 1 and get 1.00%. Entering -1, I get -1.00%.

~ Camieabz ~

All Connection Data ~ Some plusnet links

mod'er·a'tion n.
Synonyms: temperance, restraint, modesty.
Standard User XRaySpeX
(eat-sleep-adslguide) Sat 23-Feb-13 12:43:59
Print Post

Re: Negative percentage cell in Excel 2010


[re: camieabz] [link to this post]
 
Must be my old version.

Interestingly, my -1 becoming -100% is the only one that is correct grin ....

On the basis that entering 0.5 gives 50% then entering 1 should give 100% wink

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User MHC
(sensei) Sun 24-Feb-13 19:02:47
Print Post

Re: Negative percentage cell in Excel 2010


[re: XRaySpeX] [link to this post]
 
In reply to a post by XRaySpeX:
Must be my old version.

Interestingly, my -1 becoming -100% is the only one that is correct grin ....

On the basis that entering 0.5 gives 50% then entering 1 should give 100% wink



No, if the cell is already formatted as percentage then inputting any number should return that number with % afterwards.


Inputting 1 and then formatting as %age will give 100%


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

M H C


taurus excreta cerebrum vincit
Standard User MHC
(sensei) Sun 24-Feb-13 19:06:59
Print Post

Re: Negative percentage cell in Excel 2010


[re: GeoffB] [link to this post]
 
Always better to use teh Formatting function and not conditional.

##0.00%;{Red}-##0.00% given by XRaySpex works perfectly provided you change the {} around Red to []. You can tweak it for as many DPs and SFs as you wish.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

M H C


taurus excreta cerebrum vincit
Pages in this thread: 1 | 2 | (show all)   Print Thread

Jump to