Everyone works with floating point numbers daily.
If you go at a supermarket, the prices are like this: 11 eur 29 cents, 23 eur 35 cents and etc. As a C# developer and a blogger, I knew that there is some kind of a problem with the floating points and that the accuracy, but I did not realize how big it was. I thought that it only happens on some floating point numbers or similar.
The truth is unfortunately not a pleasant one – it happens a lot in plenty of calculation with floating points. E.g., if you sum 0,10 with any number above 3, you would definitely not get number + 0,10.
E.g. 10,1-10 is not 0,1!
And that’s enough to say that the calculations are wrong.
Actually, this is not only a bug in Excel, that’s a well-known bug in every computer language, due to the logic of the floating point numbers. In integer representation, every number is represented like the sum of the powers of 2.
E.g., the number 43 is represented as 1+2+8+32. Or (2^0+2^1+2^3+2^5). Not more and not less than exactly 43. What happens, when we want to present decimal numbers? The idea, behind the floating point numbers, is that a floating point numbers are represented like this: 1/2 + 1/4 + 1/8 etc. Thus, the number 0.875 is easily represented as 1/2+1/4+1/8. Or 1/2^1+1/2^2+1/2^3. Quick and simple. And nice.
However, it is really nice to have a number like 0.875, which can be represented easily as sum of 1 divided by some power of 2. But what happens, when we are not that lucky? And we have a number like 0,1? Which is rather often, when we work with decimal? The answer is only one – the computers do not calculate exactly but they give some kind of approximation. And this approximation leads to some interesting facts, that are against mathematics. For example in Excel 12.1-12 is different from 0.1. The reason for this is that 12.1-12 is not 0.1, but 0.99999999999996, because of the problem with the presentation of the floating point numbers. And this is different from 0.1. See the formula yourself:
You may ask yourself, how big is this problem? The answer is – at least as big as infinity. I will confess, I really thought that it does not happen so often, until it happened to me 🙂 Thus, I have made a small research and even built some code to help me calculate ranges of the problem. Here is what the code calculates:
With other words, for every number, between 4 and plus infinity, with a step of 0.1 Excel (and all the computer languages using doubles) makes an error in a simple calculation.
Simple as this one – 5.6-5.5. It is equal to 0.0999999999960 and not 0.1. In the next calculation, 5.7-5.6 we get 0.100000000000001 which is also not 0.1. I have decided to make the code, just to see how many “mistakes” are out there and it turned out that they are really in every number. Check it yourself:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
Option Explicit '--------------------------------------------------------------------------------------- ' Method : ErrorsNumber ' Author : v.doynov ' Date : 06.04.2017 ' Purpose: Model to see how excel calculates floating point numbers. '--------------------------------------------------------------------------------------- ' 0/2 + 0/4 + 0/8 + 1/16 + 1/32 +0/64 + 0/128 + 1/256 + 0/256 +1/512 +0/1024 + 0/2048 ' 0,099609375 '--------------------------------------------------------------------------------------- Public Sub ErrorsNumber() Const DIFF_DEFAULT = 0.1 ThisWorkbook.PrecisionAsDisplayed = False Dim lngEndNumber As Long: lngEndNumber = 30 Dim dblStarter As Double Dim dblEnder As Double Dim dblDiff As Double Dim lngCounter As Long Dim lngCounter2 As Long Dim lngRow As Long Dim dblResult As Double Dim lngCountErrors As Long Dim myCell As Range If lngEndNumber > 10000 Then Debug.Print lngEndNumber & "is too big, it takes too much time!": Exit Sub Call OnStart Cells.Clear For lngCounter = 0 To lngEndNumber dblDiff = DIFF_DEFAULT For lngCounter2 = 0 To 9 dblDiff = DIFF_DEFAULT * lngCounter2 lngRow = lngRow + 1 Set myCell = Cells(lngRow, 1) dblStarter = lngCounter + dblDiff dblEnder = lngCounter + dblDiff + DIFF_DEFAULT dblResult = dblStarter - dblEnder myCell = dblStarter myCell.Offset(0, 1) = dblEnder myCell.Offset(0, 2).FormulaR1C1 = "=RC[-1]-RC[-2]" myCell.Offset(0, 2).NumberFormat = "0.00000000000000000" myCell.Offset(0, 3).FormulaR1C1 = "=IF(RC[-1]=0.1,"""",""X"")" Next lngCounter2 If lngCounter Mod 100 = 0 Then Debug.Print lngCounter Next lngCounter With Range("E1") .FormulaR1C1 = "=COUNTIF(C[-1],""X"")/" & lngEndNumber * 10 .NumberFormat = "0.0000%" End With Columns.AutoFit Debug.Print "READY!" Call OnEnd End Sub Public Sub OnEnd() Application.ScreenUpdating = True Application.EnableEvents = True Application.AskToUpdateLinks = True Application.DisplayAlerts = True Application.Calculation = xlAutomatic ThisWorkbook.Date1904 = False Application.StatusBar = False End Sub Public Sub OnStart() Application.ScreenUpdating = False Application.EnableEvents = False Application.AskToUpdateLinks = False Application.DisplayAlerts = False Application.Calculation = xlAutomatic ThisWorkbook.Date1904 = False ActiveWindow.View = xlNormalView End Sub |
That’s true, decimals are not exact. And should not be compared.
Anyway, there are always ways to get around this. Excel has introduced the following:
|
1 |
ThisWorkbook.PrecisionAsDisplayed = True |
It works quite ok, rounding the numbers to what you see. In C#, we have the decimal variable, which is created exactly to cope up with this problem. And in any other language, we have a work around this. But still, before you see it, you never realize how big this problem is. 🙂 Here is what Microsoft says about the floating-point arithmetics.