Pivot Table - When 0 isn't really 0

[polarisking]

I have a simple pivot summing on one field. The source numbers sum to 0 for each field subtotal, but the Pivot Table subtotal is a very small number on the order of .0000000000-4.

I've provided a sample showing the behavior. My actual file has several hundred unique fields with about 1/2 of them equaling 0. I'm attempting to put a filter on the Sum results and display only those not equal to 0, but these "offenders" are leaking through (note: most of the net 0 values are in fact 0). Any help as to why Pivot Table is behaving this way would be appreciated.

 

[kchiba]

Hi Polarisking,

Try formatting the cell to Numbers, with 0 decimal places.

kanti

 

[NARAYANK991]

Hi ,

A couple of points :

1. The following thread might be of interest :

http://forum.chandoo.org/threads/zero-calculation-shows-as-exponential.19485/

2. If you choose to format the cells , either through Format Cells or through clicking on Number Format , the formula bar will still display the small value though the cells will display 0 ; as an alternative , you can insert a Calculated Field , which will use the ROUND function. This will display the value as 0 both in the cells and in the formula bar.

Narayan

 

[polarisking]

Narayan, you're right, formatting, pardon the pun, only masks the issue. I was trying to avoid a calculated field, but I may have to resort to it.

Kanti, thank you for you response.