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 the focus and the tang
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 thinking d
Read more

Slice of Advice: What have I learned this year?

Image
I recently submitted a short clip for Craig Barton’s podcast on the theme of Slice of Advice: What have I learned this year?  In this I suggested two things that had had an impact on me this year: the use of graphing apps on smartphones and diagrams representing functions as mappings between number lines. Graphing apps on smartphones I’ve been using GeoGebra for over ten years now and it has had a huge impact on how I think about mathematics and teaching mathematics. I’ve also been massively impressed with Desmos though I don’t know it inside-out in the way I do with GeoGebra. My use of these graphing tools, and Autograph before it, was always based on using them on a computer and this means I have a preference for the computer software over the phone apps: I find them much more usable on a large screen with a proper keyboard and mouse.  This works fine for me but, as I’ve commented on here before, I think the greatest impact of technology in the mathematics classroom is as a resu
Read more