I have a pivot table in Excel and the sum of the items should equal zero. Instead I am getting a number very, very close to zero (-3.63797880709171E-12). As @Tim Williams pointed out in his comment this is a known artifact of decimal <--> binary floating point conversions.
My main concern here is really in the formatting of the result. I am trying to apply an accounting format where a $0 shows as $ -
as opposed to $0.00
. The $ -
format is important because it allows you to look at a glance and see which columns "tie out" when comparing sets of numbers. When some zeroes display as $0.00
and others display as $ -
this is much more difficult.
For example,
╔══════════════════════════════════════════════╦══════════╗
║ Formula ║ Result ║
╠══════════════════════════════════════════════╬══════════╣
║ = 1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1 ║ $ 0.00 ║
║ =-1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1 ║ $(0.00) ║
║ 0 ║ $ - ║
╚══════════════════════════════════════════════╩══════════╝
I would like the result for all three formulas to show as $ -
. Is there a way to force that to happen? Perhaps there is a way to force sums in a pivot table to round to the nearest penny (i.e., hundredth)?
Try changing your formula to this so that it rounds to the nearest 10th (change the last 1 to something bigger if you need more precision).
= round(1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1-0.1, 1)
Edit: If you are unable to change the formula, you can also use a custom display format For the result along the lines of:
[<0.05]"-";[>-0.05]"-";$0.00
I have not tested this, but you should get the idea.