Zero calculation shows as exponential

[Ronald Wilson]

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.

 

[Somendra Misra]

Hi,

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.

Regards,

 

[Luke M]

Have you checked your source data? Or, if it's a calculated field, might be good to force precision down to 2 decimals via the ROUND function.

 

[Ronald Wilson]

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.


Any ideas why this is not zero?

 

[Somendra Misra]

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.

Regards,

 

[Ronald Wilson]

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.

 

[Somendra Misra]

@Ronald Wilson

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.

http://chandoo.org/forum/threads/10-21-10-0-1999999999999999999.19335/

Regards,

 

[NARAYANK991]

Hi Ronald ,

There is an Excel option called :

Set Precision As Displayed

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 :

http://excellenttips.wordpress.com/2012/08/22/excel-the-dangers-of-set-precision-as-displayed/

http://www.dustinwheelercpa.com/2011/07/06/excels-set-precision-as-displayed-option/

http://www.mrexcel.com/forum/excel-questions/515617-set-precision-displayed-off.html

Narayan

 

[Ronald Wilson]

I was able to get this to work by adding ROUND to my formula.
Instead of
=IF(REV= 0, 0, (Margin/REV))
I am using
=IF(ROUND(REV,4)= 0, 0, (Margin/REV))

This seems to be working.

Thanks.

 

[pecoflyer]

https://support2.microsoft.com/kb/78113