How to avoid negative zero currencies

[bwroga]

I have a spreadsheet with a category name in one column and dollar amounts in the second column. The cells in the second column are formatted as “Currency” “USD $ English (USA)” “-1234.00” (in red).

I then created a pivot table where the “Row Field” is the category and the Data Field is the dollar amount. When a category sums up to zero, -$0.00 is often shown and if I click the cell, the value is some very small negative decimal number such as -0.000000000000454747350886464

How can I get these zero values to not be presented as negative? I tried enabling “Precision as Shown”, but this did not help.

None of the amount fields I have entered have more than two decimal places.

[PKG]

Please upload a corresponding example file.

[sokol92]

@bwroga

When a category sums up to zero, -$0.00 is often shown

Try this number format:

[>-0.0005]"$"0.00;[RED]-"$"0.00

[bwroga]

Here is an example file:
NegativeZero.ods (11.5 KB)

[Lupp]

It’s a bug.
The relevant error is the value of the sum for “category A” in the pivot table which is -2.27373675443232E-13 in the above by @bwroga attached example sheet independent of the formatting. Using the SUM() function for the amounts of “category A” you get an exact zero.
It seems the pivot-table unit uses a different and less clever way to sum where the FPU-calculated result may depend on the order of the addends in critical cases.
The phenomenon itself concerning FP-arithmetics with fix binary length is well known. It is a case of “numerical cancellation” and can be avoided in such a case by summing the positive values and the negative values separately before a final addition.
The interpretation of a result of small amount gotten by a sum including large amounts must, however, always be checked for validity. It may anyway be insignificant.
To suppress the visibility of the effect with the help of formatting should not be the best idea.
See attached example.
anotherCaseOfCancellation.ods (17.3 KB)