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.
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.
Leave a comment