0

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)?

mwolfe02
  • 23,787
  • 9
  • 91
  • 161

1 Answers1

1

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.

lnafziger
  • 25,760
  • 8
  • 60
  • 101
  • Those three formulas I showed were just to illustrate one half of the problem. The other part of the problem is that this is specifically an issue for me in a **pivot table**. If you know of a way to round the sum in a pivot table, that would likely solve my problem. – mwolfe02 Sep 04 '12 at 14:34
  • Can you round the results before they are fed into the pivot table? – lnafziger Sep 04 '12 at 14:37
  • 1
    Your "Edit" did it for me. I had to adjust it a bit to get it to work, but I was finally able to do that. Here's the final number format I used: `[>=.005]_($* #,##0.00_);[<=-.005]_($* (#,##0.00);_($* ""-""??_);_(@_)` – mwolfe02 Sep 04 '12 at 16:29