We help IT Professionals succeed at work.

Excel sheet calculation problem

rodynetwork
rodynetwork used Ask the Experts™
on
See the attached sheet.  When I select column C, the sum is 5.68434E-13. Why?  The sum should be zero.  Normally I can click on C and it will give an accurate sum.  What do I need to address? 51003EE.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013

Commented:
Not sure that you can change what it shows on the status bar....that just reflects the value you get if you use the formula = SUM(C:C)

That amount is a number very close to zero, the reason it isn't zero is to do with the way Excel calculates - it can sometimes result in small "precision errors". You can get the correct sum in a cell by rounding e.g.

=ROUND(SUM(C:C),9)

regards, barry

Author

Commented:
Hi Barry,

Ket me clarify what I am asking. I have for several years now simply clicked on the C column and the sum of that column appears at the bottom of my window.  That always works and it is always accurate. A few days ago something happened where I import a few lines of data and every since then the C column has that weird " 5.68434E-13" instead of the actual number.  Here's another wierd thing: if I delete a row, it will sum correctly. Any row, not just a particular row, but delete any row and the sum works.  Ok, now that I am articulating this, I think I may realize what you are saying....are you saying the weird " 5.68434E-13" is excels way of saying "zero"?
Most Valuable Expert 2013
Commented:
Yes 5.68434E-13 is "close to" zero...it's equivalent to the following

0.000000000000568

You can see that if you use =SUM(C:C) in any cell and then format that cell as number with 15 decimal places. You often get that sort of result when you sum negative and positive numbers which you expect to equal zero. See explanation in this link

regards, barry
Top Expert 2010
Commented:
rodynetwork,

>.are you saying the weird " 5.68434E-13" is excels way of saying "zero"?

Not quite :)

Excel, like Access, VB6/VBA, and many other apps and programming languages uses a common IEEE standard for floating point math which under most normal circumstances is very good, but is unable to represent every single possible floating point value within its scope.

As a result, occasionally an operation that you think should result in zero instead results in a very, very tiny yet non-zero number.  (5.68434E-13 is basically "5 ten-trillionths", an unimaginably small and yet non-zero number.)

This is not a bug per se, but rather a consequence of using that IEEE standard, and it is why in some cases you will see something like this:

    If Abs(Variable1 - Variable2) < 0.000000000001 Then

Open in new window


instead of:

    If Variable1 = Variable2 Then

Open in new window

Author

Commented:
You guys are complete geeks. Thanks for the help!