Excel Pivot Table "bug"?

I have an Excel pivot table that is acting very strangely in Excel 2007 - SP2.  I've never seen this before.  When I first created the pivot table, I noticed one of the entries showing up as "-5.32907E-15".  When looking at the detail, this field should have been zeros.  So, then I added another level of detail below the total.  When I expand the detail, I get a zero total, but when I collapse it, I get the above value.

Anyone seen anything like this before?


cjaggerAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
For more info, you can get several good explanations here:http://www.bing.com/search?q=excel+ieee+floating+point&src=IE-SearchBox&FORM=IE8SRC
0
 
cjaggerAuthor Commented:
Attached is a very simple spreadsheet that I created that shows the problem.
excelbroke.xls
0
 
Patrick MatthewsCommented:
It's not a bug--it's a quirk in how Excel (and Access, VBA, and many other apps) do floating point math.The short version: the IEEE standard many apps use for floating point arithmetic cannot capture every single number to an arbitrary level of precision, which leads to situations like you see above, where you expect an operation to return a zero value, but you instead get a very tiny but non-zero value.
0
 
cjaggerAuthor Commented:
That is very strange.  In all the Excel workbooks I've done, I've never seen this before.  Thanks for the explanation and the resource!
0
 
Patrick MatthewsCommented:
cjagger,You're most welcome!It is why sometimes in VBA code, instead of a test like this:    If VarA - VarB = 0 Thenyou instead see:    If Abs(VarA - VarB) < 0.0000000001 Then:)Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.