Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT
1) If you look column “D”, of my original spreadsheet,
2) how many decimals you see there ? Answer = 14 , correct ?
3) So, why as a part of your “logical argument” you say “....If you
increase the decimals displayed to tye maximum, you'll see that the
number that looks wrong is actually 5.99999999999 or whatever, not
6.0 .......”
4) That is not an argument because that is exactly part of the
statement/definition of the problem that I described !
Column “D” was already "in the original matrix" with many decimals to
show that there is a “BUG”
5) Now, Why there is a bug ?
There is a BUG because the “addition” (+) property does not work stable or 100% perfect under all circunstances.
Demostration :
If I have X = 0
-) Then, if I say X = X + 0.1 ( value of X ? 0.10000000000000 )
-) Then, if I say X = X + 0.1 ( value of X ? 0.20000000000000 )
-) Then, if I say X = X + 0.1 ( value of X ? 0.30000000000000 )
If we continue with this excercise we “could” conclude the obvious.... The system SUM correctly ! good ! However, this is partially true.. Why ?
Because if we continue with the previous excercise we will see
that at some moment ("very quickly") this is what occurs :
a) Then, if I say X = X + 0.1 ( value of X ? 5.80000000000000 )
b) Then, if I say X = X + 0.1 ( value of X ? 5.90000000000000 )
c) Then, if I say X = X + 0.1 ( value of X ? 5.99999999999999 )
SO HERE IS THE BUG !
Why “just at point c” (after around 60 iterations !!) the value of “X” displayed show 5.99999999999999 and NOT 6.00000000000000 ??
Probably some one like you could say “yes”, that is because in reality at point “b” the value is not exactly “5.90000000000000” .... maybe internally have a less number? ( ie : 5,899999999999999999 ?!! )....
Nice explanation ! For me this is a clear bug !
I did the following calculation in another language ( MS Visual FoxPro )
With the same representation of the problem that I described. That is :
X = 0
For j = 1 to 200
X = X + 0.1
? X , INT(X)
EndFor
What was the result ? Answer : Always show the correct numbers !!
Note 1 : Regarding your “solution” : =INT(TEXT(D57,"0.00"))
Sorry, but for me that is “ugly”.. (not accepted solution.....)
( I like solutions that attack the root of the problem not
patchs for the symptom.. )
Anyway, I was not asking for a solution.
I was just describing an error/bug of excel. So maybe in the futur
in next releases of excel we could have a more "solid" tool
or maybe the error will show up not after 60 iterations....
maybe at Thousands of iterations...
Note 2 : There are “many” perfects solutions.... Examples :
Work with “integers” and do the division after the SUM in
another temporary variable....
ie : X = X + 1 and , Y = X/10
then X = X + 1 and , Y = X/10
etc,etc...
But again as I say I’m not asking for “tips” to solve the problem...
German