Custom Number Format - removing minus sign from result

[Mixmatch]

Hello,

I have a problem with number formatting. I am trying to make numbers display in the following way;

If a number is equal or greater than +1 then it will display as +x and the number will be in blue

If a number if negative and greater than -1 then it will display as -x and the text is in red

If the number falls between these two values I want it to display the text "Near Zero"

Here are the resulting formats in the right hand column.

<TABLE style="WIDTH: 91pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=121><COLGROUP><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 40pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=17 width=53>Number</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=68>Format</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>+1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>+4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17>0.5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>Near Zero</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17>-0.5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>-Near Zero</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>Near Zero</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>+5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=17>-1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>-1</TD></TR></TBODY></TABLE>

Here's the number format...

[Blue][>=1]+#,###;[Red][<=-1]-#,###;[Black]"Near Zero";

The format works for positive numbers, but as of the table above it also shows a leading minus sign or negative values. I have tried to get rid of this sign, but have been unable to, does anyone have a suggesion?

Many thanks in advance

John

 

[markf5998]

Assuming "Number" is column A and "Format" is column B, and you just have a formula in b that is =a1 etc... with the format applied to column B:

You could replace the formula in column b with:

=IF(A3>0,A3,IF(A3>-1,A3*-1,A3))

 

[Mixmatch]

Hi markf5998,

Thanks for your reply, unfortunately I really need to do this using custom number formatting rather than a formula - I included the 2 columns to highlight what is being entered in the first column and what the number format result I'm getting i.e. a leading minus sign in the "Near Zero". I'm charting In PowerPoint and it's for colleagues to use without needing to know what's occuring, so I'd rather not have a fomula even if it's the easiest solution.

Kind regards

John

 

[ydyoda]

Hello Mixmatch,

I have the same problem and I solve it by adding a "Conditional Formatting".
Keep your Custom Number Format [Blue][>=1]+#,###;[Red][<=-1]-#,###;[Black]"Near Zero";
And add a Conditional Formatting rule =IF(F19<0,IF(F19>-1,TRUE,FALSE),FALSE) where F19 is the first cell of selection and set the Custom Number Format "Near Zero";"Near Zero";"Near Zero".
It's not pretty, but cells still number cells without formula.

Kind regards,
Yo