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.

Comments

Post a Comment

Popular posts from this blog

Maths GIFs: parabolas

Image
One of the fantastic features of dynamic graphing software is that you can animate how a graph changes as some aspect of it is varied. I'm particularly keen on making short GIFs of animated graphs that demonstrate interesting properties.  One advantage of animated GIFs is that they are really easy to distribute via Twitter. There are loads of them if you have a look at the hashtag #mathgif .  Parabolas I've been posting animated Maths GIFs to Twitter for quite a while now. Here are some of my favourites featuring parabolas: The midpoint of the points of intersection of y  =  x ² and y  = mx  + c lies on the line x = m /2. The tangent at point P to a parabola with vertex Q can be constructed by finding R, the point of intersection of the vertical through P and the horizontal through Q, finding the midpoint, M, of QR and then drawing the line through P and M. If the tangents at two points A and B on a parabola are perpendicular then the line AB goes through t...
Read more

The problem with Powerpoint for teaching maths

There are a lot places on the internet where the are negative opinions about Powerpoint.  I don't want to repeat these arguments here, instead I wish highlight particular problems relating to the use of Powerpoint in the teaching and learning of mathematics.  These are that: It reinforces a view of mathematics that it is a series of algorithms to be rote-learned; It can reduce the amount of student-centred use of ICT in learning mathematics; It is usually a static form of mathematics and there are many easy tools for creating equivalent dynamic forms of mathematics. Reinforcing a limited perception of mathematics Many Powerpoint presentations for mathematics feature a question with the stages of a solution presented.  This can have a negative impact of students' perceptions of mathematics.  There is a link between students' perception of mathematics and how successful they are.  Students who perceive maths a series of unrelated recipes for solving probl...
Read more

Desmos Classroom Activities: An ideal tool for remote/blended learning

Image
The past few months have been a very chaotic time for teachers and students. Many schools and colleges are now back to teaching full time but this year is likely to be very challenging and teachers want to know that they can provide opportunities for students to learn, even if the normal day-to-day routines are disrupted. For me, one tool stands out as particularly effective in ensuring that students have interesting and engaging activities that will develop their mathematical understanding: Desmos Classroom. Desmos Classroom Activities  Desmos Classroom activities are collections of screens that provide students with the opportunity to interact and respond to mathematical questions. They can feature text answers, mathematical answers, graphical features, multi-choice, card sorts and many other types of elements.  There is a large selection of pre-made activities and it's really easy to adapt these or create your own. These activities are great for supporting students in think...
Read more