Just for a change, it's me that's looking for an answer. Something strange happened during a recent Excel 2007 course when I was demonstrating how to make one cell use the content of another one and compare it then put a statement in a box depending on what the result was. For years I have been using something along the lines of =if(A1=0.01,"right","wrong"). Pretty simple stuff but it didn't work!
Investigating, it appeared that instead of A1 containing something like 0.01 which was the result of some basic adding and multiplying it actually contained something like 0.0099999913. So I was getting wrong when it should have been right.
We looked at other numbers on the sheet and lots of them were similarly odd. Even though we'd entered just numbers with at most two decimal places in cells and none of the calculations could have led to intermediate steps in the calculations (just adding or subtracting or multiplying by whole numbers) some had lots of extra decimals and were nearly the right number and when rounded showed the right number but weren't stored by Excel as the right number.
Now, unless I have been really silly or the coffee and biscuits had a detrimental effect on not only my sanity but also that of some pretty talented delegates, this is bizarre to say the least. I guess I should do a search for the answer but thought I'd share it with you just to show that there are questions I can't answer! No doubt there'll be more on this soon!
1 comment:
Hi, this is 2 years old and im not sure if u've found the answer by now or if you'll even see this but. This phenomenon is caused by imprecision in non-integer numbers. A computer stores a floating point number( that is, a number that requires a decimal point) in a way similar to scientific notation. The number is stored as a mantissa and a multiplier. However, because both mantissa and multiplier are in base two they can never achieve 100% accuracy when representing a decimal number.(in essense, most non-integer base-10 numbers are actually irational in base-2) Thus when it is converted back to base10 to display on ur screen there will be errors.
Post a Comment