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

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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

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

>.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
```

instead of:

```
If Variable1 = Variable2 Then
```

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial