CODEWITHSUNDEEP
excelexcel-2010
Blue Magister
Blue Magister

Reputation: 13363

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?

Upvotes: 1

Views: 203

Answers (1)

David Zemens
David Zemens

Reputation: 53623

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:

Conditional Formatting Rule

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.

Custom Font Format Rule - Conditional Formatting

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>

Upvotes: 2

Related Questions