Excel 2007 : Rounding Error in Excel calculations

**dav1129** · 10-13-2010, 10:34 PM

I have used conditional formatting to show a cell in bold red when it is not equal to zero.

So I could not understand why if:

The formula for B3 is A3-C3-D3;
A3=242.57 C3=111.23 and D3=131.34; and
The result should be 0

Excel displays the result in bold red until I increase the number of decimal places to 14 and discovered Excel was calculating the result as -0.00000000000003

WHY IS THAT?

The same formula does not produce this behaviour for values

**karenbr** · 10-13-2010, 11:54 PM

You could try adding the ROUND function so that it doesn't go out to 14 decimal points. I've attached a sample that works.

**dav1129** · 10-14-2010, 02:42 AM

Originally Posted by karenbr

You could try adding the ROUND function so that it doesn't go out to 14 decimal points. I've attached a sample that works.

thanks for the reply and suggestion. i'll try it. but i don't understand why if you get zero when you do the arithmetic mentally or with a calculator but excel does not.

why is excel even getting -0.00000000000003 and not zero or 0.00?

ps tried your suggestion and in essence. it's no good because if the result is within 0.49 either side of 0.00 I don't see it. and the whole idea of this (larger) spread sheet is to pick up and identify discrepancies.

i come back to 'why is excel even getting -0.00000000000003 and not zero or 0.00? when the result should simply be 0.00.

**ConneXionLost** · 10-14-2010, 02:48 AM

It's an older article, but the situation hasn't changed, and it does describe "Rounding Errors" very well.

**dav1129** · 10-23-2010, 07:52 PM

Originally Posted by ConneXionLost

It's an older article, but the situation hasn't changed, and it does describe "Rounding Errors" very well.

Thanks for the explanation. Although I don't at the moment have the time to read and importantly understand the explanation given it would seem it details the correct explanation.

Nonetheless I have a simple formula which in this case is equal to:

242.57 - 111.23 - 131.34

Done on a calculator this clearly should equal zero. Excel is also showing this a 0.00 (rounded to two decimal places) but if in fact, you format the cell to round to 14 decimals Excel thinks the result is -0.00000000000001 as so on as you increase the number of cells Excel should the result to.

Because I have series of cells with conditional formatting to highlight results which are NOT equal to zero, the consequence that even rounded to 2 decimals Excel showing the result in BOLD RED.

So I other than try to ignore these errors I don't know how to handle them.

**Paul** · 10-23-2010, 11:28 PM

If your decimal precision never needs to exceed, say, 3 decimal places, then round all values to the 5th decimal place. This will handle almost all discrepancies due to this type of Excel rounding errors.

=ROUND(A1,5)