After rounding, how do I render negative zeroes like positive zeroes?
When I format these two values:
like this formula in Custom formatting:
_($* #,##0.00_);_($* (#,##0.00);
$ (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?
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.
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.