Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

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?


0
cjagger
Asked:
cjagger
  • 3
  • 2
1 Solution
 
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
 
Patrick MatthewsCommented:
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:
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now