We have two different versions of Excel at my office. One is the Office 365 version run on our local machines, the other is Excel 2016 that we use through our SaaS provider. They each give a different answer to the sum of the following numbers:
The numbers add up to zero, but since there are two decimal places, floating point is used by the computer and the answer is not quite zero according to Excel, at least in the order that they are shown. My home version of Excel and the local version at work both say the total is 1.67333e-12, while the SaaS Excel says the total is -8.38192e-11. Google Sheets correctly gives the sum as zero.
If I sort the data in my home Excel sheet to be in either increasing or decreasing order, the sum value reported changes to zero. If I start with the original order given above and make a few swaps, it very often changes to zero, but not always. For instance, moving the second to last number to the last spot results in zero, but swapping the second and third to last gives the same 1.67e-12. I picked a number at random (1155.88, the 8th to last number) and moved it to last, and now the sum is 4.3201e-12.
So I know about floating point and why the answer can be different from zero on a computer when it should be zero, but I’m very confused why the order matters, and why the numbers in the same order in a different program version gives a different sum as well. Can anyone provide me with an explanation?
Floating point addition is not associative (i.e., (a+b)+c may not equal a+(b+c) — the order of operations matters), and also (you are probably aware of this) the “default” is binary rather than decimal floating point, so use decimals, or fixed point/integer arithmetic if you really want decimals)
Try e.g. 0.1 + 0.3 + 0.00001 both ways; you may get results like 0.40001 vs 0.40001000000000003
OK, so the problem is that there’s a slight error on each step due to finite precision with numbers that aren’t expressible in powers of factors of two, and what that error is depends on the exact two numbers being added together, such that it fails to be associative?
I guess I really never considered anything about how the system worked, only that the system meant that on occasion sums of numbers that I knew had to equal zero came out to some really small number instead.
Right. Some floating point numbers can be added exactly, and some others might add up in ways that round up or down depending on what’s adding to what.
Yes, your numbers like 0.1 are not represented exactly, so there is a small error in approximating them. If you naively just keep adding/subtracting them, your error will grow in proportion to the square root of the number of summands (I think).
Also, think about adding 1.0 + HUGE + 1.0 - HUGE; if you do not do something about the loss of precision you will get 0.0 instead of 2.0.
NB, again, it is possible for business stuff that your numbers are not really (or not supposed to be) floating-point numbers at all, decimal or otherwise, in which case the solution is obvious: don’t compute that way.
So I was thinking about this, and it seems to suggest that I should be able to find some order in my home version of Excel that will come out to the same value as what the SaaS Excel gets, since it’s presumably just adding the cells in a different order to get its different answer. The most obvious place to start would be in reverse order I guess.
We always round to the nearest cent when importing from Excel because of this. Either explicitly when the program doesn’t like more than 2 decimals, or the program just truncates it. Since we don’t do anything other than addition and subtraction, I don’t see a problem. Also, tests for zero are also whether a number is less in absolute value than e-8 or something like that, which comes up reasonably enough since getting a bunch of numbers to sum to zero is the primary goal in lots of accounting.
Microsoft has the reasoning at Floating point may give inaccurate results. This is a problem with nearly every computer that uses binary math. In my Java programming class I made sure to explain the IEEE-754 standard and the problems with accuracy and resolution. A common test or interview question is “How do you determine that two floating point numbers are identical?”, since as you discovered 0 and 0.0000000000001 can be considered equal in most cases. In programing, the solution is see if the absolute value of the difference between the two numbers is “close enough” for you.
The problem may be that, without a lot of digging, we simply do not know what algorithm different versions of Excel use to add up a column of numbers. Maybe it is different across the two versions of Excel, not that I imagine the exact error value makes any practical difference, as long as it is as small as expected. If your error is only e-12 or e-11 for that list of numbers, it actually does not seem messed up. The machine precision is probably something like e-16 and you are adding 4- or 5-digit numbers.
Now a lot of accounting involves adding or subtracting, but I can imagine there are situations where you may need to divide as well and therefore a fixed precision of 0.01 may not magically solve all your problems). But if you can simply round -8e-11 to zero then there may not be a problem at all—or are you adding millions or billions of figures and experiencing some discrepancy?
It doesn’t. Try using the formula:
=SUM(A1:A38)*1000000000000
I get:
1.673328143
In short, it’s all because 1/5 in binary is a repeating fraction, just as 1/3 in decimal is 0.3333… It can’t be represented exactly because some digits get cut off.
Ideally, there would be a proper decimal data type that stored an integer scaled by some decimal fraction. But neither Excel nor Sheets seems to support this.
You’ll probably just have to set your rounding mode to account for the small error.
Not necessarily. Machine precision is not quite an exact thing. x86 machines actually use 80-bit floating point registers by default, which means they can sum lists of numbers with higher precision than they come in with. Input and output is 64-bit.
That is… unless they are using SSE instructions, or have a special floating-point flag set, or have just emulated the math, or a zillion other things…
Also do not forget that none of the numbers in the cell are actually what you see on your screen or showed us in your listing in your OP. And the numbers on the SaaS computer are different numbers than the numbers on your PC. Even though they appear to be the same numbers as rounded for display, they are different internally.
That, far more than order of evaluation, is the source of your problems.
How best to correct that depends very deeply on what these numbers represent, where they come from, etc. Nobody here has the information from you (yet) to give a good solution to your problem. That depends on what those numbers are in the real world represented by your spreadsheet model. Many of us could solve your problem correctly, once we have the requisite info.
Say, wasn’t there a new number representation introduced a few years back at a very low level, which was IIRC a 128 bit representation that was an integer multiplied by 0.01? That is, integer math in pennies, for American and other currencies that are quantized at 0.01?
128-bit decimals were officially added to IEEE 754 in 2008. That gives you 34 digits of precision instead of about 16, and it’s decimal. However, it is still a floating-point format, so I am not sure about your integer math in pennies. If you really just need an integral number of pennies, you could perhaps just use a 128-bit signed integer (not sure what is the current low-level hardware support for that, but for an accounting ledger, who cares?).
Speaking of such things, weren’t there some early electronic pocket calculators programmed to actually calculate in base ten, including the non-iterative algorithm for division that students are taught? So, they wouldn’t convert between base two and base ten, I mean.
There certainly were and still are processors that performed math directly on base-10 numbers using base-10 logic. I can’t speak to pocket calculators specifically.
It seems you have at max two decimal places. If this is money, I believe Excel has an option to encode things as money/currency, which might use a fixed decimal. And, even if it doesn’t, it should round it off.
Since you’re not doing division or multiplication, it would be safe to round off to the maximum precision you enter.
This https://www.hpmuseum.org/techcpu.htm
says that HP calculators use(d) 56-bit registers with 10 (decimal) digits and a 2-digit exponent. As for identifying the algorithms implementing division, inverse hyperbolic tangent, etc., perhaps the source code for the firmware is on github somewhere?