## Excel can’t add up! (Oh yes it can)

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, horror, **is not zero. So, clearly there is a bug, Excel sucks and we can all have a happy rant about Microsoft’s incompetence. Right?

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) and type

-127551.73+103130.41+1807.75+7390.11+9028.59+2831.26+1568.90+1794.71

The result?

8.6402e-12 – exactly the same as Excel.

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.

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 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).

In fact, when working in double precision, 0.1 is approximated to

0.1000000000000000055511151231257827021181583404541015625

Which you can see in Matlab by typing

fprintf(‘%.55f\n’,0.1)

You can see the effect of this if you do the following calculation in something like Octave or Matlab

(0.1 + 0.1 + 0.1) – 0.3

the result of which 5.551115123125783e-17

If you need to learn more about this sort of thing then the Wikipedia page on IEEE arithmetic is quite good and so is this article from the Mathworks.

Good article.

I think Matlab is being very naughty printing all those extra non-0 digits in fprintf(’%.55f\n’,0.1). It gives you misleading ideas about the internal precision. Consider: from the first ‘5’ digit onwards it could just print random digits and that would represent exactly the same floating point number. Strunk and White (“How to Print Floating Point Numbers Accurately”; Guy L. Steele Jr., Jon L White; ACM SIGPLAN’90 Conference on Programming Language Design and Implementation; 1990-06) suggest printing “****” when the printing very large numbers of decimal places and the internal precision has been exhausted. So printing “0.10000000000000000**************************************” instead. Of course C probably can’t do that and still claim conformance.

A related article of mine discusses how many digits are necessary, in Python.

Hi David

Matlab is doing exactly what I told it to do – I asked for 55 digits in the format specification and it gave them to me. I guess you could think of this as “ask a stupid question – get a stupid answer”. In normal usage, if you want to print the maximum number amount of precision then you would type

format long

before proceeding with your calculations. This gives 15 to 16 digits of precision and so would not print any of the stuff from the 5 onwards. It still prints 94.799999999999997 instead of 94.8 though

I am glad that it never tries to hide what is going on internally if I push it by using something like fprintf because it serves as a useful demonstration for what is actually going on ‘under the hood’. I am far from an expert in this sort of thing but it seems to me that if you can never get at all of the digits of the number that is actually stored in hardware then it would be difficult to explain the results of calculations like

x=0;

for k=1:100000000

x=x+0.1;

end

disp(x/100000000)

>>0.099999999811295

I think this is horrible behavior, particularly in Excel.

Matlab users, perhaps, should be educated about rounding error.

Excel is quite commonly used for financial calculations. So two-place decimals should be two-place decimals. Why not store decimals as decimals internally? We have enough memory and computing power to handle that nowadays.

My preferred language these days is DrScheme, where if you type 0.1 it internally stores it as 1/10 (and in general, any decimal is stored as an exact decimal fraction). If you want the floating point approximation (for speed, or so denominators don’t get huge in a long calculation) then you tell it you want an inexact 0.1 by saying #i0.1. And similarly in its output it will flag numbers that might have rounding error by printing #i in front of the number for you.

I performed the sum -127551.73+103130.41+1807.75+7390.11+9028.59+2831.26+1568.90+1794.71 in Open Office and note that it gives 0 to 20 decimal places so it seems that Open Office is doing something different!

@admin: Well, obviously you should be able to get all of digits that are represented internally, and I never said you shouldn’t be able to. I was suggesting that you should not be able to get any more, because the internal representation doesn’t really have any more to give. They’re just misleading. And printing 94.8 as anything other than 94.8 is just stupid and wrong. Python, Matlab: stupid and wrong.

By the way, I should hope that Matlab “format long” also gives 17 digits of precision as that is required for some numbers.

Printing floating point numbers poorly indicates to me that the authors (of Matlab and Python, say) haven’t been doing their homework properly. And they really ought to.

I’ve fixed real bugs in real software because some people got this wrong. Meh. That’s why I like to rant about it.

Hi David

Sorry about that then – I misunderstood you. As for ‘format long’ – I only know what the docs tell me.

Umm. That documentation is scary. Can I pester you and ask you what 1.0000000000000073 prints out as in matlab using format long? It’s the smallest double bigger than 1 that requires 17 digits of precision.

Um, your loop to 100 million example… why isn’t it printing the correct answer of 0.09999999981129451 ? (Note: 0.09999999981129451 and 0.0999999998112945 are not the same double)

Am I scaring you enough about Matlab floating point printing yet? I’ve scared me, plenty.

David

You are not pestering me at all.

>> format long

>> 1.0000000000000073

ans =

1.000000000000007

As for your other question – it just missed off the last digit. This can be fixed with fprintf

fprintf(‘%.19f\n’,x/100000000)

0.0999999998112945099

Thanks this has all been very interesting. So in conclusion, Matlab, if left to its own devices (that is, disp and “format long”), doesn’t always print enough digits to display the number correctly. Correctly in the the weak sense that it prints out some number which reads back to yield the same number.

I would say that for software positioned at the high-end scientific market that’s absolutely unforgivable.

Just fell over this after 5 years of no comments…

You are wrong: Excel can’t add.

Using floating point for calculations isn’t necessarily recommended and for it to make an arbitrary decision on your part rather than maintain precision is unforgivable.

Fire up an HP calculator (preferably an RPN/RPL device) and enter the same things. You will get zero.

Fire up a pencil and paper and enter the same things. You will get zero.

This is the machine’s poor non-decimal FP implementation leaking into user-space unexpectedly. This is poor.

Just because standard IEEE FP stores approximate values, doesn’t mean it’s what the user wants to see or expects.

Octave/matlab is forgivable as you can specify the precision and it’s designed for approximate calculations (computations) over symbolic and numeric consistency (which is what you do on paper and decent CAS systems).