Microsoft Excel Pivot miscalculation in total for positive and negative numbers

Question

I'm not sure if I am doing something wrong (maybe yes), or there is a problem in Microsoft Excel.

Scenario: I create a table below

+------+-------+ | Name | Value | +------+-------+ | Alex | 1.55 | | Alex | -1.56 | | Alex | 0.01 | +------+-------+ 

When I try to get the sum of the values ​​in the code, the result is as follows:

 +-------------+--------------+ | Row Labels | Sum of Value | | Alex | -8.67362E-18 | | (blank) | | | Grand Total | -8.67362E-18 | +-------------+--------------+ 

I expect that [Sum of Value] will display 0, but will display a non-zero value (-8.67362E-18).

Thank you in advance

Answer 1

Although this is normal floating point behavior, see https://support.microsoft.com/en-us/kb/78113 and https://support.microsoft.com/en-us/kb/214118 , however it ugly as it behaves differently in pivot tables and normal excel sheets.

Sheet:

The formula in B5 is

= SUM (B2: B4)

As you can see, the result is 0.

Summary table:

As you can see, the normal calculated amount is not 0. And no, pivot tables will not take into account the "Accuracy displayed" setting.

What I've done?

Added calculated field "Feld1", see https://support.office.com/en-us/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea-8128b5e904b8#bmcreate_a_formula , s the formula:

= ROUND (value, 8)

and used it for the amount.