|
|
|
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.
|
|
|
I presume you have tried conditional formatting??
Zen 8000 Pro
|
|
|
##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.
|
|
|
As Pipexer says, use conditional formatting to format cells that are less than zero to suit.
|
|
|
|
Ah, yes! I'd used this before to highlight certain cell values but hadn't realised that it was so powerful. Thanks everyone.
|
|
|
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%' 
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
|
|
|
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%.
|
|
|
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% 
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
|
|
|
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
|
|
|
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
|
|
|
inputting any number should return that number with % afterwards. So you're saying inputting 0.5 in to a %-format cell results in '0.50%'?
Cm'on, admit it is inconsistent.
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
|
|
|
Why do we need '{}' instead of '[]'? Did it change sometime?
SF?
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
|
|
|
Why do we need '{}' instead of '[]'? [red] is a BBCode tag- it wouldn't have shown, and turned everything after into red text!
|
|
The author of the above post is a thinkbroadband moderator but it does not constitute an official statement on behalf of thinkbroadband.
|
|
|
Indeed, whereas {} brackets are used in Excel for array formulas (and probably other things I haven't discovered as yet).
|
|
|
inputting any number should return that number with % afterwards. So you're saying inputting 0.5 in to a %-format cell results in '0.50%'?
Cm'on, admit it is inconsistent.
Cell formatted for percentage:
Entering 0.5 actually displays 0.500% (3dp set)
but entering .5 gives 50.000%
-0.5 gives -0.500%
-.5 gives -50.000%
There is certainly inconsistency.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
M H C
taurus excreta cerebrum vincit
|
|
|
I was referring to the string in my post ... I just could not remember the that was needed in front.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
M H C
taurus excreta cerebrum vincit
|
|
|
Sorry, misread your post. Thought you now need '{}' in Excel.
You needed [noparse] in front.
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
|
|
|
Entering 0.5 actually displays 0.500% (3dp set)
but entering .5 gives 50.000% True, that's inconsistent.
Actually I did enter .5 in Excel but typed 0.5 when writing my post to make it clearer, never imagining that they would be any diff.
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
|
|
|
If you read my previous post, it does not make sense, but try doing a Quote and you will see that I put [noparse] in there - once, but it needed to be there twice!
This bottle of Swiss Red wine is having an effect.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
M H C
taurus excreta cerebrum vincit
|
Pages in this thread: 
Print Thread
deleted
