Pivot table returning E value eg. -2.1316282072803E-14, value should be 0 (zero)

[Kasey07]

Hi

Hi

Looking for advice and help with Pivot table, I have several values that should be 0 (zero) but instead they are E values (for example -2.1316282072803E-14), and so I cannot filter them out as not a true Zero. 

The total of the values below should be zero, instead I get -2.1316282072803E-14 ....

-98.355
37.148
44.085
-17.123
-34.245
68.49
-7.955
-44.672
44.672
-71.596
65.326
-42.676
43.302
-13.599
-27.19
-54.389
108.777

Grateful for any advice. Thank you!

[#2 · Accepted solution]

@Kasey07  ... 49 views and not one response yet!  I'm surprised.

This is really a Pivot Table question.  And I'm afraid that I know nothing about PTs.

The infinitesimal difference (-2.13E-14) is a common arithemetic issue (*).

It arises because most decimal fractions cannot be represented in binary "floating-point", the internal representation of numeric data in Excel.  And the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number.  That is why, for example,

IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!).

In general, the work-around is:  when you expect a calculation to be accurate to some number of decimal places (probably 3 in your case), explicitly round to that number of decimal places -- and not to an arbitrary number of decimal places like 10.

But I do not know how to make that happen in a Pivot Table, in general.  And someone who is knowledgeable about PTs might not be able to explain how unless and until you provide an Excel that demonstrates the problem.  Use the "browse" link at the bottom of a reply frame; or upload the file to a file-sharing website, and post the download URL in a response here.

A dubious alternative is to set the "Precision as displayed" option (PAD).

It is "dubious" because there are some risks when that option is set, and because it does not always remedy the problem because it applies to the final value of formulas per se.  In particular, I do not know if setting PAD remedies such problems with Pivot Table calculations per se.

In any case, if you want to experiment with setting PAD, be sure to make back-up copy of the file first, because merely setting PAD might change some constants irreversibly; and that might adversely change the results of some calculations unintentionally.

-----

(*) We do not see an infinitesimal difference by summing the numbers that you posted.  Nevertheless, it is possible to happen with a different set of numbers.

I suspect that one or more of the posted numbers is the result of calculations; and it is those calculations that differ infinitesimally from the result as it is displayed.

Both potential problems should be addressed by an appropriate change to the Pivot Table.

Again, I'm sorry that I do not know enough (anything!) about PTs to offer better guidance.

Hopefully, someone who knows PTs will respond soon.  I suspect the remedy has something to with "calculated fields" in the PT.

[#3]

Thanks @Joe User !

I did some research and saw the PAD option which I am not keen to do, as you say it is dubious. 

I was hoping there is a solution as the total does equal absolute zero. Thanks again.

[#4]

@Joe User Thank you!!! You put me on the right track. I created a calculated field - SUM field and round to 3 decimal places - and then filtered on Values that do not equal 0.00 and it worked YAY!
Thanks so much for your help!

[#5]

It helped me sole same issue.

[#6]

I had the same issue but for currency values. Changed the Number to Currency and issue resolved!! Thanks for the insights!