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]

 

[Yard]

How do you KNOW the values add up to zero?

 

[Norie]

Alex

This is a known bug of computers, especially when dealing with floating point number arithmetic.

 

[xenou]

Agree with above posts - most likely one of your numbers in the source data is actually also something like 12.6160000000000001

(in which case, to test, use a formula on the source to data and replace the existing values with rounded values, then refresh the PT).

 

[Gerald Higgins]

I think Norie's on the right track. It's a known "bug"/"feature" of Excel. One way to get round it is to use rounding.

 

[vizechecker]

OK, so the data was input manually, that means 12.616 is just 12.616, not a single 10^-999999 more or less So there's nothing to gain by rounding the values, because they are already rounded. (I double-checked rounding and replacing them, though ) Now I know that the values I entered *would* add up to Zero, but I used the SUM function on the raw data and it says it's not Zero...
But how can this be a standard bug of computers, that they are unable to calculate the sum of more than 3 numbers that have 3 floating point numbers I hope you have some more ideas for me, that would be great!

Thanks so far @all,
Alex (feels like a computer-newbie now )

 

[vizechecker]

Check this out:
Enter this as a formula
=174.025-24.975-24.975-24.975-24.1-25-25-25
and this
=174.025-24.975-24.975-24.975-24.1-75
where I just replace 3 times -25 by 1 time -75. The result is not the same, the first gives my [FONT=宋体]2.84217E-14, the second just gives Zero. I think I'm getting crazy [/FONT]

 

[Norie]

visechecker

It is a standard bug of computers and how they store/calculate numbers.

Computers use 1s and 0s, which are whole numbers.

So when it comes to working with decimal numbers they try to use methods to convert those numbers, and they aren't always exact.

I can't give you a comprehensive explanation, but perhaps this will help Accuracy problems.

 

[vizechecker]

Norie,

thanks. I mean I know about the 1s and 0s, I just never imagined that these (for me) simple calculations pose a problem to a computer.
So my only option would be to round all the raw data, but then it just might give wrong results, too (Start with 5.4, take away 2.7 and 2.7, and I would have minus 1 ending inventory if the data was roudned to zero decimals).
Can I somehow ask the Pivottable to round the subtotals down to integer numbers? Or would I have to link the pivottable to another sheet and round down every cell that belongs to a subtotal?

Ah, I love being intolerant towards computers' problems stupid black box

 

[Norie]

I don't think rounding the raw data would be a problem, it should be straightforward.

The floating point thing has been a problem ever since computers were invented.

I think once back in the eighties I tried reading up about the theory invovling mantissas etc.

Anyways pivot tables are an excellent tool but you do need to ensure the data source is stuctured well.