Pivot Table Creates Inaccurate Decimal Values


G

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;
 
Ad

Advertisements

G

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
 
R

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
 
B

Bernard Liengme

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

Advertisements

Joined
Jul 3, 2018
Messages
1
Reaction score
0
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...
 

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