CELL j = CELL j-1 + 0.1 => demostrated This NOT WORK

[gvicenci]

Excel_error2.xls
	A	B	C	D	E
2		GermanVicencio		X	Integer(X)
3				0,00000000000000	0
4		Incredible....ButTRUE!		0,10000000000000	0
5		MSEXCELdoesNOTSUMcorrectly.		0,20000000000000	0
6		Ifyoucreateasimpleformulaliketheone		0,30000000000000	0
7		incolumn"D"(CELLj=CELLj-1+0.1)		0,40000000000000	0
8		Youwillrealizethatexcellosecontrolafter		0,50000000000000	0
9		repeating/filldowntheformula		0,60000000000000	0
10		andgenerateWRONGresults!!		0,70000000000000	0
11				0,80000000000000	0
12		Accordingtothis:6=5??!!!!		0,90000000000000	0
13				1,00000000000000	1
14		Unfortunatellyduetothelimitationsinsize		1,10000000000000	1
15		forpostingontheboardIcan'tshowexplicit		1,20000000000000	1
16		theevidence.However,ifyoufilldown		1,30000000000000	1
17		theformulain"D"and"E"youwillhavethe		1,40000000000000	1
18		evidenceofthisbig!Error!		1,50000000000000	1
19		AfterRow62theworksheetget"crazy"		1,60000000000000	1
20		SorryBillGates.......Butyourprogrammer		1,70000000000000	1
21		didabasic"math"errorhere!!		1,80000000000000	1
22				1,90000000000000	1
23				2,00000000000000	2
24				2,10000000000000	2
25				2,20000000000000	2
26				2,30000000000000	2
27				2,40000000000000	2
28				2,50000000000000	2
29				2,60000000000000	2
Hoja1

 

[PaddyD]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

hi - welcome to the board.

not quite sure I follow from the example, but I think the issue you allude to stems from implementing floating point arithmetic in a binary environment. Excel's decimal precision is limited to 15 digits. For more on this, see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q78113

and

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q48606

paddy

EDIT1: to add hedge after re-reading OP's post & Eli's repsonse
EDIT2: which he's now deleted

 

[PaddyD]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

Via PM:

PaddyD,
Thanks for the answer.....
I believe I diden't express correctly the problem ( I speak spanish...)
so you diden't understand exectly the issue....
But this is not related to floating point arithmetic, etc,etc ...

This is just a BIG bug in excel.....

Give me your eMail and I will send a small spreadsheet ( 30 kb)
that demostrate the FACT.

Or if you prefer do the following :
1) Create a Cell ( ie : D2) with value 0 ( zero)
2) In Cell D3 put the formula =D2+0.1
3) Copy/filldown D3 to complete the column ( until row 100)
4) In Cell E2 put the formula =INTEGER(D2)
5) Copy/filldown E2 to complete the column ( until row 100)

Then, you will see/verify the error after ROW 60......

You're english is infinitely better than my spanish, so no worries there (by the way, we do support an international forum...)

To the question....I followed your example, & it is related to precision errors. 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:


In the following, the first 2 columns are calculated the same, but have different numbers of decimals displayed. The next 2 columns have the respective int() formulas:

Book1
	C	D	E	F
57	5.5	5.50000000000000	5	5
58	5.6	5.60000000000000	5	5
59	5.7	5.70000000000000	5	5
60	5.8	5.80000000000000	5	5
61	5.9	5.90000000000000	5	5
62	6.0	5.99999999999999	5	5
63	6.1	6.09999999999999	6	6
64	6.2	6.19999999999999	6	6
65	6.3	6.29999999999999	6	6
Sheet1

The "odd" line is 62. While this is in some senses expected, I guess it's hard to predict when it will happen without elaborate calculations about mantissas etc...Here, if returning the correct integer from the calculation is important, you can compensate for the decimal errors with something like:

=INT(TEXT(D57,"0.00"))

...sorry, but I do not know the spanish version equivalent.

paddy

 

[gvicenci]

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

 

[PaddyD]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

If you wish to call this a bug, that's up to you. Nevertheless, it is a matter of design, related to the implementation of floating point arithmatic. For fear of reinventing the wheel, I refer you to the following by Chip Pearson:

http://www.cpearson.com/excel/rounding.htm

and in particular, the paragraph:

"Fractional numbers are stored in a similar manner. In the binary system, fractional numbers are stored as the sum of a series of fractions: (x*1/2 + x*1/4 + x*1/8 + x*1/16 etc) where x is the state of the bit. If the bit is on, x=1. If the bit of off, x=0. Unlike integers, however, not every fractional value can be stored exactly accurately. For example, it is impossible to store the number 1/10 = 0.1 in binary form. A close approximation is (0*1/2 + 0*1/4 + 0*1/8 + 1*1/16 + 1*1/32 etc). Computers carry this operation to the equivalent of 15 decimal places. Even with this accuracy, many numbers are represented as an approximation of their "true" or "analytic" value. For example, it is impossible to accurately describe the number 1/10 in 8-byte (or any length) binary notation. Floating point numbers can come extremely close to representing that number, but there will always be some very small error. "

 

[gvicenci]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

1) Facts :
In many other good languages don’t occur this problem/bug.
In MS FoxPro 2.6 ( 16 bit) don’t occur this problem
In MS Visual FoxPro 8 ( 32 bit) also don’t occur this problem
Also in others this problem don't show up....
2) You say
“it is a matter of design, related to the implementation of floating
point arithmatic”

3) I Say :
“it is a matter of (good or bad) design, related to the (good or bad)
implementation of floating point arithmetic”

4) The important is “the result”.. and in this case is a wrong result ( bug !)
You could give many explanations why a tenis player diden’t
pass the ball over the net..... fine !....
but if the result was wrong you lose the game....... then you need to
train more and maybe next time the ball will pass.

5) Today there are many “intelligents” implementations of “math”
in computers..... even you could have “symbolic algebra” in some
packages..... that solve “complex” equations,
symbolically, even complex expressions, etc,etc...... (mathLab, etc)

So, accept very easilly that X = X + 0.1 will FAIL after 60 iterations
and say that is perfect according to the explanation of some one
named “Chip Pearson ” that explain “floating point jerga” .......
Nice joke......
FAIL after 60 iterations in a very simple number like 0.1 IS A BUG !!

6) I’m sure this problem will disapear or NOT be as obvious as today
in excel in next versions...
Then, you will say that they use "another" technology and that is the
reason that the problem don't show up again ?
7) From any point of view, this particular situation is a BUG

 

[PaddyD]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

The lecture is unnecessary. The processor, which is what does the calculations, not excel, performs the arithmetic accurately + exactly. However, the arithmetic is performed on the stored value, ie the 53 digit binary approximation to the number you started with. It is the binarys that are inaccurate, not the arithmetic operations performed on them.


Call it a bug if you want, but it is a predictable effect of excel's design. Moreover, it is an effect that is unavoidable in any binary environment. From a programming point of view, therefore, the question is not how to avoid the "bug", but how to develop code that addresses the issue appropriately. This will always be required as long as computing is done on discrete architecture, and is independent of the particular software / bit size / hardware configuration.

 

[gvicenci]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

“….The processor, which is what does the calculations, not excel,
performs the arithmetic accurately + exactly…..”
Irrelevant not related to this conversation/analysis. Why ?
If I have a payroll software/package that don’t “sum” correctly
and generate a wrong check to the employee
(ie : salary $ 300 + bonous of $ 477 and generate a total of $779 instead of $ 777), is clear/obvious that even the processor ( intel or other) work fine, there is a problem/bug in the “payroll SOFTWARE”.
This is the nature of software …. This is why we have versions, releases , services packs.
The “tennis racket ” could be perfect but NOT ALL the players that use the same racket/intel are champions.
( FoxPro use the same “intel” ….. but in this particular set play better than excel)

Here we are talking about “software” (EXCEL ) that have a bug in the “sum” operation already described/demostrated. ( the SUM was not accurate under this circunstances )

All you are saying in your messages is explain with the “BUG” occurs.
Fine, that is another conversation maybe useful for the people that will need to work to solve this bug in the futur or “minimize” the effect. So in next versions the behavior of the “SUM” will work in the same way that others software packages ( ie : foxpro, others,etc.)

Notes :
a) “….but it is a predictable effect of excel's design…...”
fine, but be more accurate with the wording …. The effect in this case is not wished and the appropiate name is “bug” due to the fact that the design need to be improved. (no good in this case)

b) "…..effect that is unavoidable….. “
NO!, I’m sure that engineers of MS will minimize or eliminate this
bug in the futur. ( other software don’t have this effect / bug )

c) ‘… From a programming point of view, therefore, the question is not how to avoid the "bug", but how to develop code that addresses the issue appropriately. … ‘

again, be more accurate and avoid contradictions :

“addresses the issue” = “addresses the bug”

You could call “issue”. I call “bug” ……..

final note: "Computer Programming" is an "art" not a science...
See books of Donald Knuth

 

[PaddyD]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

whatever.

 

[tusharm]

Re: CELL j = CELL j-1 + 0.1 => demostrated This NOT

As Paddy wrote, whatever.

However, if you are serious about understanding this issue and not just ranting about MS, see http://support.microsoft.com/default.aspx?scid=kb;en-us;78113.

The problem exists in every numerical computing system. Take paper and pencil and write down 1/3 in decimal. Use as many digits as you want. Now, do it again. And one more time. Now add the three numbers. What do you get?