Excel 2010 PivotTable Oddity

[Ausnadian]

Hi All,

I have a very strange excel issue I was able to reproduce on two
computers using Excel 2010. To reproduce the issue, do the following:

1. Paste the following info into a new Excel file:

Type Amount
Invoice 616
Cheque -616
Invoice 199.36
Cheque -199.36
Invoice 199.36
Invoice 67.2
Invoice 71.19
Cheque -337.75
Invoice 84
Invoice 100.8
Invoice 100.8
Invoice 106.51
Cheque -392.11

2. Create a PivotTable from this data.
3. Set up your PivotTable like this:
i. Row Labels - Type
ii. Values - Sum of Amount
4. Look at the Grand Total number

On my systems, it's showing the grand total is -2.27373675443232E-13,
despite the fact that the sum of the above numbers is in fact 0.

Any idea why this is happening and/or how to fix it?

Thanks,
Ausnadian

 

[Ausnadian]

Hi All,

I have a very strange excel issue I was able to reproduce on two
computers using Excel 2010. To reproduce the issue, do the following:

1. Paste the following info into a new Excel file:

Type    Amount
Invoice 616
Cheque  -616
Invoice 199.36
Cheque  -199.36
Invoice 199.36
Invoice 67.2
Invoice 71.19
Cheque  -337.75
Invoice 84
Invoice 100.8
Invoice 100.8
Invoice 106.51
Cheque  -392.11

2. Create a PivotTable from this data.
3. Set up your PivotTable like this:
 i. Row Labels - Type
 ii. Values - Sum of Amount
4. Look at the Grand Total number

On my systems, it's showing the grand total is -2.27373675443232E-13,
despite the fact that the sum of the above numbers is in fact 0.

Any idea why this is happening and/or how to fix it?

Thanks,
Ausnadian

Here's a link direct to the Excel file: http://dl.dropbox.com/u/4127527/strange.xlsx

 

[Bruce Sinclair]

Hi All,

I have a very strange excel issue I was able to reproduce on two
computers using Excel 2010. To reproduce the issue, do the following:

1. Paste the following info into a new Excel file:

Type Amount
Invoice 616
Cheque -616
Invoice 199.36
Cheque -199.36
Invoice 199.36
Invoice 67.2
Invoice 71.19
Cheque -337.75
Invoice 84
Invoice 100.8
Invoice 100.8
Invoice 106.51
Cheque -392.11

2. Create a PivotTable from this data.
3. Set up your PivotTable like this:
i. Row Labels - Type
ii. Values - Sum of Amount
4. Look at the Grand Total number

On my systems, it's showing the grand total is -2.27373675443232E-13,
despite the fact that the sum of the above numbers is in fact 0.

Any idea why this is happening and/or how to fix it?

Look up 'rounding error'.
Briefly, ASCII coded decimal numbers cannot usually be represented by
accurate base 2 numbers (ones and zeros) as computers need to do,so they
approximate and, what you see is usually the rounded result. I'm guessing
your -2.27373675443232E-13 (a really small number, particularly if you are
dealing with money ) is the bit left over from the calculations.

Suggest trying a format change (from what you've typed above, maybe currency
is a good choice ?). Alternatively, try a fixed decimal of some sort.
Does that "fix" the problem ?