Excel incrementing up for no reason - ARGH!

[TomH]

Hello. Any help will be GREATLY appreciated.

I have attached an excel file in .xls format so I could post it, but I am working in v.2010. The file shows, in column F, formulas that add .01 to the value in the cell above, but starting with the 100 in E1. Column G shows the value of the cells as they were calculated by my excel 2010. I added that in the case that you open the file and for some reason your version correctly calculates the values in column F.

Note I've hidden numerous sections of rows. I've color coded column D to show where the breaks are. Note that in rows 98, 294, 489, 685 and 880 that the value increments up by an additional 0.000000000001 for absolutely no reason at all. Now, the number of rows between this phenomenon may be a clue as to what is happening... 98, 196, 195, 196, 195... but I have NO IDEA why this version of excel would perform this way.

I discovered this problem when I was laying out increments for a chart x-axis in tenths from -5 to 5. I noticed that the zero value was formatted in exponential and other cells along the way were subjected to this incrementation.

So... my question is two-fold. First, what is going on here? Second, how do I stop it?

THANK YOU for any insight.

Tom

 

[NBVC]

See http://support.microsoft.com/kb/78113

you can "work around" with ROUND()

e.g.

=ROUND(F1+0.01,6)

copied down.

 

[TomH]

Thanks. I have no idea whatever what floating-point arithmetic is, but I'll take your word for it that it's related. I've used ROUND to get around this numerous times and it indeed does work. It's extremely irritating that I'll have to remember that for every case in which I use VLOOKUP, MATCH, etc. from now on. Seems stupid to me.

Thanks for your help.