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

 

[Yogi Anand]

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

Hi German:

Adding to what Paddy and Tushar have stated, another way to look at it is, in nature there is continuum, we human beings model reality in one fashion or another, and in working with computers, we model the true continuum of nature by digitizing -- and the binary numbering system used in computers, although models the reality pretty well, but we know it is not perfect. With the knowledge that it is not perfect we devise ways to have our way of working with a system come as close to reality as we can make it happen.

All processes that we use are our attempt to model reality the best we can -- when we don't have a closed-end solution, we settle for a conservative solution considering the boundary conditions, the upper limit, the lower limit, and so on. What you have discovered is the confirmation and awareness that despite our best efforts, the most we can do is model(mimic) reality. Any more on this and we will drift into metaphysics.

 

[Legacy 12630]

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

From any point of view, this particular situation is a BUG

Since there have been other points of view expressed as to what this particular situation is, it would have been "more accurate" to have typed "From my point of view, this particular situation is a BUG".

With regard to your hypothetical salary example of an amount of $779 being paid instead of $777, at least that's better than receiving an underpayment - particularly when it's such a small salary anyway

Also :- You could call “issue”. I call “bug"
Wasn't there a song some years ago about a tomato that went something like this?

 

[Legacy 12630]

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

To : Yogi Anand

What have you been smoking, and where can I get some?

 

[Yogi Anand]

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

Hi Ponsonby:

Am I to understand that you do not agree with what I said? -- and if so -- what is the part that you don't agree with? -- perhaps you may be refering to my statement that all our methods and processes that we use try to model reality.

In any event, if you would care to, please tell which statement of mine you disagree with, and share with us your own opinion on this matter.

 

[PaddyD]

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

"...drift into metaphysics"

Narcotics are neither a necessary nor sufficient condition for metaphysics, but they certainly help!

 

[gvicenci]

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

1) “....Just ranting about MS...”
Wrong. Don’t be bias/emotional.
Read the complete sentences/ideas and not just the ones that you want to read. I say clearly that “MS FoxPro and MS Visual Foxpro” don’t have this “issue” ( bug ). ( and you already know MS).
I like very much MS and the smart people ( Bill Gate , Paul Allen and many others).
What I don’t like are the “bugs”....... “mediocrity”... things that are not very perfect and could be more perfect.
I attack problems not people or institutions.

2) “....understanding this issue...” ( “....understanding this bug...” )
Yes, this is the bottom line : You and Paddy are addressing another point of view, different to the one that I’m addressing.
You and Paddy are trying to explain/understand.... “why” occurs “this bug”. Fine!,
You could participate in a research team to maybe contribute in the analysis.
I’m just describing, presenting the fact (in this case a BUG). So in future versions of excel this issue will disappear or be reduced to a minimun. ( with a more intelligent/smart algorithm..)
As today implementation is not acceptable that after "60" iterations of a simple expresion
X = X + 0.1 we obtain a wrong result.
( again, in other languages we don’t have this bug..... so is possible to have a better method !!! )

3) “....The problem exists in every numerical computing system.....”

Sorry, but I don’t agree. This is your personal opinion and could be limited by many factors.
That problem does not exist in other languages. ( or if exist is reduced to a minimun ...)
Will depend of the “algorithm” that is behind the language. And the quality of the “algorithm” will be directly related to the “creativity/intelligence” of the programmer or software engineer.
The “algorithm” used today in excel ( that refer specifically to this situation) is not good and generate this bug. But I’m sure that people of MS will correct this problem in the near futur.
The world advance because there are people (not many... but severals) that will never accept “dogmatic/intimidated” sentences like
“... problem exist..”
Maybe a problem/limitation/bug “exist today” ..... but not necessary tomorrow.... and that is the challenge.
As Einstein say “Imagination is more important than knowledge...”
As Donald Knuth say this is an “art” not a science.
So stop with dogmatic sentences that present "limitations of today" as if they will be for ever!
In the past, 30 years ago we have languages that have many limitations ( ie: use of line numbers and GOTO ### ). Do we have that limitation today ? NO!
So.... In the futur I'm sure this problem will not exist.
Also, the particular implementation in excel today is so poor/coarse (extreme...BUG) that will need urgent improvement in the short time..

German V.

 

[parry]

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

GV, from a person who defintely doesnt know about floating point arithmetic, and has no real desire to, I cannot see how posting your views in such a manner is going to make MS change their code.

Although your goals are not that clear I presume your aim is to make people aware of the issue (you have done that if people read this) and get MS to amend their software. Considering its up to ver 10 or so of Excel and they specifically posted an explanation on their site of how they treat FP operations, then I dont think they plan on changing this any time soon.

I think your comments may be better served in a white paper to MS as you appear to have strong views on this issue.

 

[Legacy 12630]

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

Hi Ponsonby:

Am I to understand that you do not agree with what I said? -- and if so -- what is the part that you don't agree with? -- perhaps you may be refering to my statement that all our methods and processes that we use try to model reality.

In any event, if you would care to, please tell which statement of mine you disagree with, and share with us your own opinion on this matter.

I most certainly do not disagree with what you "said" !!!!
That is not possible since it's way over my head and I just don't understand it. Merely thought that if I could have some of the same stuff, things would be clearer.

 

[Legacy 12630]

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

To : GV

I think you should stop beating around the bush and post what you really think about the situation.

 

[gvicenci]

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

To Ponsonby

i) In this discussion/analysis the first message out of context came from you :
“……What have you been smoking, and where can I get some?…..”
That kind of statement does not add any value to the analysis. ( and as a joke…. just + - )

ii) Now, you say :
a) “….I think you should stop beating around the bush …” and
b) “….post what you really think about the situation…”

Answer for a) : GOTO i)

Answer for b) : GOTO the first messages. It Is very clear the description of the situation and what I think.
There is a bug….. and I believe MS will correct this BUG in the near futur.

iii) Recap :
In general almost all the people refer to this FACT as “expected” under “Floating-Point Arithmetic implementation “
So they are explaining “why” this situation occur. I’m not doing this.
I’m just presenting the fact/bug.

My opinion : The implementation of “IEEE 754 specification” by MS, particulary in excel is not full perfect. Why ?
because in the example that I give :
“X = X – 0.1“ the precision “is lost” too soon….. After 60 evaluations is lost.

This hypothesis was confirmed empirically with MS Visual FoxPro where the same case work without problem. (so, “IEEE 754 specification” implemented better )

Therefore, there is a bug in excel.

Note : There is another situation worse to the one presented here.
Try this in excel : 1.0 * ( 0.5 – 0.4 – 0.1 )

This is the result “ in excel” -0.0000000000000000277555756156289

Is this correct ? NO! , Why ?
Because if we follow strictly the “IEEE 754 specification” it say clearly
to have only “15 digits of precision”
So what must be the value according to “IEEE 754 specification” ?
This value ! : 0.0000000000000000

(empirical demostration : evaluate 1.0 * ( 0.5 – 0.4 – 0.1 ) in
MS Visual FoxPro. What value will show ? this :
0.0000000000000000000 ( correct )

So, Conclusion, what is the BUG (again) ….……. :

MS have a bug ( in excel ) since the implementation of “IEEE 754 specification” Is not perfect regarding the “15 significant digits of precision”
Note : MS recognize that they don’t adhere to ALL the “IEEE 754” specification.
However, obviouslly the “15 significant digits of precision” is “mandatory” and MS say that they adhere to this. ( however, they fail in this part of the implementation and this is the BUG )