Pivot Table Creates Inaccurate Decimal Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Annie Hoppstock - 29 Aug 2007 19:40 GMT

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;

Reply to this Message

Nick Hodge - 29 Aug 2007 22:45 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

> Message: I am experiencing a consistent, recreatable problem with Excel
> Pivot
[quoted text clipped - 20 lines]
> Microsoft
> as a bug;

Reply to this Message

Annie Hoppstock - 30 Aug 2007 00:16 GMT

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

> Anne
>
[quoted text clipped - 31 lines]
> > Microsoft
> > as a bug;

Reply to this Message

Ron Rosenfeld - 30 Aug 2007 00:55 GMT

>Hello Nick,
>
[quoted text clipped - 7 lines]
>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

Reply to this Message

Bernard Liengme - 30 Aug 2007 01:51 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hello Nick,
>
[quoted text clipped - 61 lines]
>> > Microsoft
>> > as a bug;

Reply to this Message

Rate this thread: