Forum Discussion
Solved
Pivot table returning E value eg. -2.1316282072803E-14, value should be 0 (zero)
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!
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.
Hello - Look for the problem within the field that contains the "tallying" formula (Results Field at the bottom of the list of values you enter)
- We have had the same issue - One field (out of a couple hundred result fields) at the bottom of a column of two decimal point 'number" entries contained this cell formula:
=SUM(H5:H4313)
That is: The total sum of all of that column's Positive and Negative entries from cell H5 through cell H4313
Somehow, some way, this particular cell's formula began tallying this cell's results as an Exponent - Instead of the total being ZERO it would give us -2.13E-14 ... the close representation of Zero we both were getting. By RIGHT CLICKING this particular results cell and selecting "Format Cells" we simply changed that results field to "Numerical" with a limit of Two Decimal places (or three decimal places in your example) - The exponential disappeared and the cell results began rounding down to two decimal places to finally yield the Zero result as this field had done for over 10 years (we continue to use an older version of Office's Excel but still have no idea how this field changed from "Number" to "General").
It looks like if you copy or move a column with this type of configuration (tally) the "new" column's tally field may loose its original "Format Cell / Number Type" when pasted, but will keep the actual formula assigned to that cell.
Apparently, Xcel Must(?) 'loose/remove' that "Numerical" specification for other references to still be valid ?? Just a guess, but this 'fix' solved the issue of the exponential replacing Zero in our case, without affecting other referenced cells.
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.
Thanks JoeUser2004 !
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.
- JoeUser2004 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!
