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

 

[kskinne]

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

i agree

 

[gvicenci]

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

To : just_jon

The solution will came from MS when they correct the bug.
There are severals ways to by-pass the bug. Here are two ways :
a) X = round( X + 0.1 , 15 )
b) or use “precision as displayed option” On the Tools menu under Options Click to select the “precision as displayed check box”

Actually “a” is a kind of “user” implementation of the IEEE specification
Of “Storing only 15 significant digits of precision”.

Regarding your statement :
“...the OP is wanting to engage in a discussion rather than a solution..“
Wrong interpretation.
Anyway, there is nothing bad with discussions as long they are objective
and attack “problems” not “persons”....
Usually solutions came from previous discussions/analysis...

 

[Yogi Anand]

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

Hi German:

You suggest that there are two possible workarounds(my word) on what you have described as a bug. Do you think that it is possible that if MS were to hardcode those workarounds in EXCEL's implementation of FP numbers, that there may be some other issues (problem areas) that might arise!

 

[tusharm]

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

OK, folks, let's give this a rest, Ponsonby notwithstanding.

This board has been blessedly free of trolls, and I realize that the uniqueness of observing one this close can be almost irresistible. But, dig down into that fortitude. Find the courage to say "No!" After all, as anyone who hangs out in the newsgroups knows, the only cure for a troll is to ignore her/him.

The OP's suggestions for workarounds come straight from the MSKB. Since he (I'm guessing it's a he not a she) has read -- without acknowledging -- what MS has to write on the subject, if he wants to delude himself into thinking MS will 'fix' this 'bug' we should let him wait in silence.

For anyone interested in this subject, there is a large body of literature on numerical analysis, numerical computing, and the study of the numerical stability of algorithms. Among the many options, none of which includes feeding a troll , are:

• search google/yahoo
  search support.microsoft.com
  get the book 'Numerical Recipes'
  read the papers/books by people like Knuth, Wirth, their contemporaries, and the follow up literature
  search the google.com archive of the XL newsgroups for posts on the subject by Martin Brown, Jerry Lewis, Harlan Grove, Mike Middleton, Chip Pearson, David Braden, (and myself?)
  take a course in numerical computing

 

[NateO]

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

Many software offerings demonstrate glitches, here are some more current Excel shortcomings (see: Statistical Function Improvements).

Here's an old Access bug. Lotus 1,2,3 had bugs, and don't get me started on Lotus Notes.

Thanks for the observations. This is now (perhaps previously ) unnecessarily taking up bandwidth and going nowhere -> Next.