Pivot Table Creates Inaccurate Decimal Values

[Guest]

Message: I am experiencing a consistent, recreatable problem with Excel Pivot
Table.
I believe it is a definite bug, but find no mention of it in Microsoft
Knowledgebase.

The pivot table modifies the values from the source by adding decimal level
detail that is not accurate.

Do the following to recreate (in Excel XP / Excel 2003):
Sheet 1: A1: Project , B1: Amount, A2: Test; B2: -18.25, A3: Test! , B3
18.01.
Create a pivot table with Project as the row, and Amount as the Data.
The formatted results will display -.24, however, if you click on the cell
and look in the formula bar, the number displayed is -0.239999999999998.
I have other situations where pivot table values are displayed even when the
total is exactly zero.

I'd like to know if there is a patch to fix this or if it is corrected in
the 2007 version; if not, I'd like to know how to report this to Microsoft
as a bug;

 

[Nick Hodge]

Anne

This is not a bug or even an Excel problem. It is to do with the way
computers handle floating point arithmetic when they actually operate in
binary whole numbers. This means any floating point number is just an
approximation

A better explanation is here

http://cpearson.com/excel/rounding.htm

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

 

[Guest]

Hello Nick,

Thank you for your response. The article you referenced was very useful.

However, I'm still concerned that Excel modifies the number I put in when I
hard-typed a specific number of decimal places. For example... I type in two
decimal point precision for two numbers and it computes a result that somehow
adds precision. If you type .03 - .01, shouldn't you get .02? I expect
the storing of of extra floating point values when Excel has to compute the
starting values, but I don't expect it when I type the exact decimal
precision.

Is there a way to force a 2 decimal number that is typed in to be stored as
exactly 2 decimals?

Thanks!

annie

 

[Ron Rosenfeld]

Hello Nick,

Thank you for your response. The article you referenced was very useful.

However, I'm still concerned that Excel modifies the number I put in when I
hard-typed a specific number of decimal places. For example... I type in two
decimal point precision for two numbers and it computes a result that somehow
adds precision. If you type .03 - .01, shouldn't you get .02? I expect
the storing of of extra floating point values when Excel has to compute the
starting values, but I don't expect it when I type the exact decimal
precision.

Using decimal notation, how would you "exactly" type in 1/3? You can't do it
as the value 1/3 cannot be exactly expressed in decimal notation.

With a computer, although you may be entering decimal notation, the computer is
storing it as a binary number to the precision specified by the IEEE standard.
Many decimal numbers cannot be expressed "exactly" in binary notation, any more
than 1/3 can be expressed "exactly" in decimal notation.

Is there a way to force a 2 decimal number that is typed in to be stored as
exactly 2 decimals?

There are some side effects, but you can use:

Tools/Options/Calculation
Workbook Options
Select "Precision as displayed"

Or you can explicitly Round your entries by using the Round worksheet function.

However, for your Pivot Table, you will have to use a custom formula that does
the rounding.

Using your example, show the Pivot Table tool bar. Click on Pivot Table and
select Formulas/Calculated Field.

Name: rSum
Formula: =round(Amount,2)

Then, instead of Amount, drag rSum to the data area.
--ron

 

[Bernard Liengme]

You could force the result with =ROUND(0.03 - 0.01,2) or =ROUND(A1-B1,2)

 

[acary]

Hello, Ron Rosenfeld's solution (inserting a calculated field into the pivot table using =Round(field,2) works great -- almost. Unfortunately, the Grand Total is still calculating the original values (pre-rounded) values -- not the post-rounded values. The result is my grand total being off by $.01. Thoughts or workarounds? Thanks much in advance...