Microsoft Excel Pivot miscalculation in Sum for po

问题:

I am not sure whether I am doing something wrong (probably yes) or there is a problem in Microsoft Excel.

Scenario: I create a table as below

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

When I try to get sum of Value in a pivot the result is as below:

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

I expect the [Sum of Value] to display 0 but it displays a non zero value(-8.67362E-18).

Thanks in advance

回答1:

While this is normal behavior with floating point calculation, see https://support.microsoft.com/en-us/kb/78113 and https://support.microsoft.com/en-us/kb/214118, it is ugly however since it behaves different in pivot tables and normal excel sheets.

Sheet:

Formula in B5 is

=SUM(B2:B4)

As you see, result is 0.

Pivot table:

As you see, normal calculated sum is not 0. And no, pivot tables will not respect setting "Precision as Displayed".

What have I done?

Added a 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, with formula:

=ROUND(Value, 8)

and used this for sum.