Summing Problem In A Pivot Table

M

Mike

I have a very simple pivot table. However I am having a problem with one
particular cell within the pivot table. The content of this one cell should
be zero. The lines that are pulling to the pivot table contain the following
amounts:
-30853.30
3021.75
3217.50
808.34
2511.79
2873.96
2267.23
1039.82
1588.35
4548.94
8975.62

All of the amounts contain omly two digits to the right of the decimal
point. When these line are summed outside of the pivot table with up to 30
places to the right of the decimal point they sum to zero. When they are
summed within the pivot table the sum to the following amount:

When they are summed within the pivot table these numbers sum to 3.63798E-12

I need them to sum to zero within the pivot table as I am doing calculations
within the pivot table and I need them to be calculated as zero.

What am I doing wrong?
 
F

Fred Smith

This looks like a rounding error to me.

First, Excel has up to 15 digits of precision in its numbers. So there's no
use using any more than 15 decimal places.

Second, what is the format being used to display the numbers, and the
resulting sum? In the pivot table, it's obviously General (or Scientific).

Try using General as the format for all the cells involved. See if that
identifies the discrepancy.

To fix the problem, use =Round(formula,2) when creating the data you are
feeding to the pivot table.

Regards,
Fred.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top