Excel VBA – Floating Point Numbers in Excel – Not Exact

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:

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:

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.