Forum Discussion
Pivot table returning E value eg. -2.1316282072803E-14, value should be 0 (zero)
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.