Technical Discussion
>> Windows Issues

Pages in this thread: 1 | 2 | (show all)

Print Thread

deleted
(deleted) Fri 22-Feb-13 17:40:06

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.

Pipexer
(eat-sleep-adslguide) Fri 22-Feb-13 17:43:59

Re: Negative percentage cell in Excel 2010

[re: deleted] [link to this post]

I presume you have tried conditional formatting??

Zen 8000 Pro

XRaySpeX
(eat-sleep-adslguide) Fri 22-Feb-13 18:09:07

Re: Negative percentage cell in Excel 2010

[re: deleted] [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)

camieabz
(sensei) Fri 22-Feb-13 18:09:58

Re: Negative percentage cell in Excel 2010

[re: deleted] [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.}

deleted
(deleted) Fri 22-Feb-13 18:46:00

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.

XRaySpeX
(eat-sleep-adslguide) Fri 22-Feb-13 19:09:17

Re: Negative percentage cell in Excel 2010

[re: deleted] [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

camieabz
(sensei) Sat 23-Feb-13 00:58:24

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.}

XRaySpeX
(eat-sleep-adslguide) Sat 23-Feb-13 12:43:59

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

MHC
(sensei) Sun 24-Feb-13 19:02:47

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

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

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

MHC
(sensei) Sun 24-Feb-13 19:06:59

Re: Negative percentage cell in Excel 2010

[re: deleted] [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