{"id":106,"date":"2008-05-13T14:40:08","date_gmt":"2008-05-13T13:40:08","guid":{"rendered":"http:\/\/www.walkingrandomly.com\/?p=106"},"modified":"2008-05-13T14:40:08","modified_gmt":"2008-05-13T13:40:08","slug":"excel-cant-add-up-oh-yes-it-can","status":"publish","type":"post","link":"https:\/\/walkingrandomly.com\/?p=106","title":{"rendered":"Excel can’t add up! (Oh yes it can)"},"content":{"rendered":"

One of the newsletters I subscribe<\/a> to described the following ‘bug’ in Excel. If you sum the following numbers by hand then you get a result of zero:<\/p>\n

-127551.73
\n103130.41
\n1807.75
\n7390.11
\n9028.59
\n2831.26
\n1568.90
\n1794.71<\/p>\n

but if you use Excel to sum these numbers then you get a result of about 8.6402e-12 which, shock, horror, <\/strong>is not zero. So, clearly there is a bug, Excel sucks and we can all have a happy rant about Microsoft’s incompetence. Right?<\/p>\n

Wrong! Excel is behaving exactly as I would expect it too and you should expect it to behave this way too. First of all let us determine that this ‘bug’ doesn’t just occur in Excel. Fire up your copy of Matlab (or the open source equivalent, Octave<\/a>) and type<\/p>\n

-127551.73+103130.41+1807.75+7390.11+9028.59+2831.26+1568.90+1794.71<\/p>\n

The result?<\/p>\n

8.6402e-12 – exactly the same as Excel.<\/p>\n

Either you come to the conclusion that 3 different development teams have produced software that can’t add up or that something more subtle is going on.<\/p>\n

The ‘something subtle’ is the fact that computers represent numbers internally using binary and when you only have a limited number of binary digits to play with you cannot represent all decimal numbers exactly. A classic example is the decimal number 0.1. The binary representation<\/a> of 0.1 requires an infinite amount of digits and so if you only store a finite number of these you will always be working with an approximation (just like when you write 0.33333333 as the decimal expansion of 1\/3).<\/p>\n

In fact, when working in double precision, 0.1 is approximated to<\/p>\n

0.1000000000000000055511151231257827021181583404541015625<\/p>\n

Which you can see in Matlab by typing<\/p>\n

fprintf(‘%.55f\\n’,0.1)<\/p>\n

You can see the effect of this if you do the following calculation in something like Octave or Matlab<\/p>\n

(0.1 + 0.1 + 0.1) – 0.3<\/p>\n

the result of which 5.551115123125783e-17<\/p>\n

If you need to learn more about this sort of thing then the Wikipedia page on IEEE<\/a> arithmetic is quite good and so is this article<\/a> from the Mathworks.<\/p>\n","protected":false},"excerpt":{"rendered":"

One of the newsletters I subscribe to described the following ‘bug’ in Excel. If you sum the following numbers by hand then you get a result of zero: -127551.73 103130.41 1807.75 7390.11 9028.59 2831.26 1568.90 1794.71 but if you use Excel to sum these numbers then you get a result of about 8.6402e-12 which, shock, […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4,11],"tags":[],"class_list":["post-106","post","type-post","status-publish","format-standard","hentry","category-math-software","category-matlab"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p3swhs-1I","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=\/wp\/v2\/posts\/106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=106"}],"version-history":[{"count":1,"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions"}],"predecessor-version":[{"id":5068,"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=\/wp\/v2\/posts\/106\/revisions\/5068"}],"wp:attachment":[{"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/walkingrandomly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}