After rounding, how do I render negative zeroes like positive zeroes?

When I format these two values:

0.0001
-0.0001

like this formula in Custom formatting:

_($* #,##0.00_);_($* (#,##0.00);

I see:

$  (0.00)
$   0.00

I can make them look the same with an IF statement and adding a small number to the near-zero negative values. Is there an easier way to make them look the same while keeping the usual behavior for positive and negative numbers?

Answers


I would just use conditional formatting.

You can use this in conjunction with the custom format you have already assigned. In the Custom Formatting UI, do this:

Then set the Number conditional formatting like this _($* #,##0.00_);_($* #,##0.00_); which will treat positive & negative values the same ONLY when the rounded value = 0.

Further information:

Formatting applies to the values, not to what the values look like after the formatting has been applied.

From Microsoft, with Custom Formatting you can specify formats for Positive, Negative, Zero, and Text values. There is no option to formulaicly derive whether a value should be treated like another type of value, without using a conditional formatting rule(s).

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>


Need Your Help

Simplify an XML comparison task

java xml csv

I'm currently working on a program which reads all elements of two xml files and compares them with each other. Afterwards it generates a csv containing the differences (this is not the problem).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.