I have a Pivot Table where amounts that total zero and are displaying zero in the cell are sometimes showing up in the Formula Bar as an exponential value. This causes other calculations in my Pivot Table to be incorrect. See cell B16 below.
In formula bar the real value comes which is shown in scientific notation and that too -13 that is very small number. What you see in cell is after applying number format, that is only a face, not the real number. The real number is shown in formula bar.
The source data is from a table with amounts with 2-digit precision. If I drill into the zero amount to the underlying table, it shows what appear to be normal amounts. If I select all except the last amount, I get this (notice the Sum at the bottom):
But once I include the final amount, it switches to exponential, instead of zero.
Hi Can you just paste these values in a new file and check the behavior, if it is repeating can you upload that file, for us to check, it is difficult to judge from a picture.
See the amount in the Pivot Table of the selected file compared to the value in the Formula Bar. I need to do a calculation based on this amount and expect it to be zero, but since it is not, it messes up the calculation.
The calculations are OK. See the file, I had added the cumulative sum column in the table to show at last you are getting 0. Also see the below link for getting a small value of the order -13, may be because of this reason.
If you know that your data source is always with 2 decimal place accuracy , then it is safe to set the above option ; for other situations where setting this option is not advised , see the following :