11/30/2010

When 2 - 1 Doesn't Equal 1

When is 0.01 not 0.01?

When Microsoft Excel says it's really 0.009999937.

I found out the hard way that's what Excel has been doing to data I've been analyzing at work. I kept getting inconsistent and outright incorrect results when I was trying to generate a histogram, used to show the distribution of the measurement errors in a circuit. This little problem kicked my butt for over two hours towards the end of my work day. What's worse is that when I went over results I'd charted last week and the week before I realized the same thing happened with those as well.

The only way around the problem was to manually enter the data values rather than using a formula to generate them. (I can see a formula causing rounding errors, but not when the formula is straight forward arithmetic and all the numbers entered have only two decimal places.)

I haven't tried this using Open Office Calc yet, but I'll be curious to find out whether this is specific to Excel or to spreadsheet applications in general.