Friday, 7 November 2014

How to Break Excel

Last weekend was the annual MathsJam Conference.  If you haven’t been (or even if you have) I highly recommend it.  For more details see: http://www.mathsjam.com/conference/

The format is talks of maximum length 5 minutes on anything you find interesting.  This year I gave a talk on “How to Break Excel”.

How to Break Excel


A commonly occurring “error” in Excel happens when you type 1 into cell A1, =A1-0.1 into cell A2 and then drag this down to cell A11.



This problems occurs due to the way Excel stores numbers: it uses floating point arithmetic with 1 bit for the sign, 53 bits for the mantissa and 10 for the exponent.  This means that the number we think of as 0.1 in base 10 is actually stored in binary as:
0.0001100110011001100110011001100110011001100110011001101

Similarly 0.9 in base 10 is stored in binary as
0.11100110011001100110011001100110011001100110011001101

This means that every time you use 0.1 in base 10 Excel is actually using:

At each stage in the subtraction Excel rounds to 53 significant binary figures.  There are two places where this introduces an error: when subtracting 0.1 from 0.8 there’s an error in the 53rd binary place and when subtracting 0.1 from 0.4 there’s an error in the 55th binary place.  This results in a total error of 2-53 + 2-55 = 1.38778×10-16

Fractional Powers of Negative Numbers


Another error occurs when you enter =(-8)^(2/3) into Excel .  It gives the result #NUM.

I think this is happening because it is rounding 2/3 in binary to:
0.10101010101010101010101010101010101010101010101010101

As a fraction this is:

The denominator of this fraction is 253, which is even.  Consequently this requires finding an even root of a negative number which isn't real!

But, if you enter =(-8)^(1/3) into Excel it does give the result -2.

I’m not sure why, but I think that a negative number to the power one over an odd number has been hard-coded in as a special case but a negative number to the power of any other number over an odd number hasn't.

No comments:

Post a Comment