Pivot Table Subtotals are Wrong - Instead of Zero, tiny amounts are displayed

[vizechecker]

Hello everybody,

I've just started using Pivot Tables and have a problem right away. Couldn't find anything with the search function, but am not really sure, what to search for, either

Part of my data looks like this

<TABLE style="WIDTH: 246pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264" width=102><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696" width=53><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264" width=102><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=102 height=19>[FONT=宋体]Name[/FONT]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=69>[FONT=宋体]Lot #[/FONT]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=53>[FONT=宋体]Bin[/FONT]</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=102>[FONT=宋体]Amount[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="174.02500000000001">[FONT=宋体]174.025 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-24.975000000000001">[FONT=宋体]-24.975 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-24.975000000000001">[FONT=宋体]-24.975 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="12.616">[FONT=宋体]12.616 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-12.616">[FONT=宋体]-12.616 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-24.975000000000001">[FONT=宋体]-24.975 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-24.1">[FONT=宋体]-24.100 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-25">[FONT=宋体]-25.000 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-25">[FONT=宋体]-25.000 [/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[FONT=宋体]ABC[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">CB18C</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="-25">[FONT=宋体]-25.000 [/FONT]</TD></TR></TBODY></TABLE>

Now this is a material that is stored on a pallet with the code CB18C. The total sum of material that is added to/taken from this pallet adds up to Zero. My Pivot Table, however, believes the total is something like [FONT=宋体]2.84217E-14. [/FONT]

<TABLE style="WIDTH: 246pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264" width=102><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1696" width=53><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3264" width=102><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=102 height=19>[FONT=宋体]Sum of Amount[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=69>[FONT=宋体]　[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=53>[FONT=宋体]　[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=102>[FONT=宋体]　[/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>[FONT=宋体]Name[/FONT]</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">[FONT=宋体]Lot #[/FONT]</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">[FONT=宋体]Bin[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">[FONT=宋体]Total[/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>[FONT=宋体]ABC[/FONT]</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>[FONT=宋体]123[/FONT]</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">[FONT=宋体]CB18C[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2.8421709430404007E-14">[FONT=宋体]2.84217E-14[/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>[FONT=宋体]　[/FONT]</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>[FONT=宋体]123 Total[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2.8421709430404007E-14">[FONT=宋体]2.84217E-14[/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>[FONT=宋体]ABC Total[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">[FONT=宋体]　[/FONT]</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">[FONT=宋体]　[/FONT]</TD><TD class=xl29 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="2.8421709430404007E-14">[FONT=宋体]2.84217E-14[/FONT]</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>[FONT=宋体]Grand Total[/FONT]</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">[FONT=宋体]　[/FONT]</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">[FONT=宋体]　[/FONT]</TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="2.8421709430404007E-14">[FONT=宋体]2.84217E-14[/FONT]</TD></TR></TBODY></TABLE>

[FONT=宋体]This problem occurs several times, the raw data adds up to Zero, but the Pivot Table calculates these tiny, tiny totals. Is this a known bug or can Pivots not handle 3 decimal data?[/FONT]

[FONT=宋体]Thanks for any help![/FONT]
[FONT=宋体]Alex[/FONT]

 

[Cindy Ellis]

You can make your pivot look right (even though there's a discrepancy in the 14th decimal) by formatting the data in the pivot to "number" instead of "general", and adjusting the number of decimals displayed to your choice. In my brief testing, the formatting held even if I refreshed the pivot.
Hope this helps.
Cindy

 

[vizechecker]

Thanks Cindy. But I need to change the result, not the look. The thing is, I would later check whether this pallet/bin location is empty so I can store new material on it. But it seems I will have to do some Epsilon-Checks.

@Norie: What I meant is, if I just round all the data, I might not come up with the "right" results, either.

Start: 5.4 -2.7 -2.7 End: 0.0
Rounded:
Start: 5 -3 -3 End: -1

But I totally agree on your opinion about Pivot Tables, I love them, now that I know that it's not their fault, but my computer's

(Actually, the link you posted, contains some complicated information, but I found it quite interesting, thanks!)

 

[Cindy Ellis]

If you're going to use the result (outside the pivot table) you can round the result (rather than the individual contributors) to 3 or 4 or however many digits are meaningful. If D15 holds the sum of your pallets, then

=ROUND(D15,3)

will round the result to 3 decimals; in the case of 5.4 - 2.7 - 2.7 you would get 0.000.

 

[xenou]

Thanks for all the info here too - I'm not used to dealing with numbers other than currency (2 decimal places), and I've not run across this "bug"/"feature" before in practical terms.

Typically you can use such a result in further operations by using a "tolerance" approach, where "zero" is defined as between the tolerance (i.e., between -0.000001 and 0.000001, or something like that). Or, in Excel, using a rounded value (round the result to 3 decimal places - as noted above and so on).

Yeah - all starting to sound like an echo in here, I guess.

 

[Vegiepie]

Hi there

Not sure if you will still see reply as it has been so long, but I had the same problem today. Pivot amount should have added up to 0, but came up with long decimal figure. Came across this post when I was trying things out, but still, like you, could not understand why just 1 figure in my large pivot would not return the correct value.

Until... I checked the number setting in the PIVOT table for the cell that was playing up. Mine for some reason had defaulted to "scientific". Changed it back to "Number" and all is sweet now!