Bizarre calculation error

[FadedRed]

Hi

I have come across a strange error in Excel -
I was creating a column of factors to multiply some experiemental data.
I started with 2.0, and each row below subtracted 0.1, to create a column from 2 to 0 in 0.1 increments.
But instead of 0, I have the result -6.38378E-16
Now this is a very small number, but it's not zero which it should be. It's not recognised as zero either - I can divide it into a number and get an answer, instead of #DIV/0

Changing the initial entry, ie, starting with a different number from 2 (1.9, 1.8 etc) gives sometimes different, sometimes the same result, but only zero when I start with 0.7 or less.

How can excel fail to get a simple subtraction right? 0.1 - 0.1 is not -3.19189E-17, it is zero.

Has anyone else come across this? I take it this is a rounding error of some sort. It shouldn't affect what I am doing right now, but I can easily imagine work that requires working at such small numbers - which could then be wrong.

 

[NARAYANK991]

Hi ,

This is a problem with Excel's usage of the floating point format , especially when doing arithmetic with values which are very close to each other.

What you can do is , instead of a simple formula such as previous value - 0.1 , try doing the following :

1. In the first cell , where ever it is , enter the starting value , say 2

2. In the next cell , enter the formula :

=Starting_Cell - 0.1 * ROW(A1)

where Starting_Cell refers to the cell which has the starting value of 2.

3. Copy this formula down. You should get a proper 0.

Narayan

 

[FadedRed]

OK thanks, that works.

I'll have to look up floating point arithmetic, that seems weird.