Extra digits being assigned, round doesn't work

[dareman93]

Okay, I have a problem that is really perplexing me. I have written an extensive VBA program that will take data sets recorded by two different instruments and prepare them for use in a piece of visualization software. One of the tricky steps involved pairing up precipitation data with the sewer level data.

The way the loggers work is like this - the sewer level logger logs the depth of flow in the sewer every 5 minutes, no matter what. The precipitation logger logs the date and time that 0.01 inches of rain falls. So, what you end up with, after a month, is 2 different .csv files that need to be merged, but they have different time scales.

My spreadsheet goes through and (among many other steps) combines the date and time into a single value for both the level logger data and the precipitation data. It then goes through each date and time that was recorded in the precipitation data (each representing 0.01 inches of rainfall) and looks through all of the date/time data in the level logger data, finds the closest one, and adds the precipitation incrament to that time step (I made the precipitation incrament a user-input variable so that it could easily be changed if, for example, another precipitation logger model was used that only recorded 0.1 inches of rain or somethign like that).

Here is where the problem comes in. I input the precipitation incrament as 0.01 inches. So, you would expect that all of the values recorded for precipitation would be some multiple of that (0.01, 0.02, 0.03, 0.10, 0.25, etc.) depending on how much precipitation fell during that level logger time step. What actually gets recorded in the spreadsheet is 0.00999999977648258 (instead of 0.01), or 0.0299999993294477 (instead of 0.03), etc.

I tried to fix it by ignoring the underlying problem and using the Round function as follows: PrecipNew = Round (PrecipExist + PrecipIncrament, 2). That should look at the existing precipitation recorded at that time step in the workbook and add the incrament. PrecipNew is then written back out to the workbook. The Round function should truncate all of the extraneous digits and spit out what I was expecting (0.01, 0.02, 0.03, etc.). It does not.

The variables are all dimensioned as Single (PrecipNew, PrecipExist, and PrecipIncrament). Has anyone else ever run into this or know why this is happening? I've spent all weekend searching and banging my head against the wall trying to fix it. Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

It may help to see your VBA code, but what you are seeing is probably caused by the shortcomings of floating point arithmetic:

Floating-point arithmetic may give inaccurate results in Excel

 

[dareman93]

Wow - I just got a headache attempting to skim through that article, let alone read it. But thanks for the quick reply!

That must be what is going on, though. Another engineer in my office just suggested I remove the dimension statements so that the variables are no longer dimensioned as Single. That worked and I now get the expected 0.01, 0.02, 0.10, etc.

The full code is very complicated and involved, but the relevant subroutine is as follows:

' This subroutine opens the Precipitation Logger file, inserts the precipitation data into the Conversion
'      Worksheet, then closes the Precipitation Logger file.

Public Sub Precipitation(ConversionWorkbook, CombinedCell, PrecipFile, PrecipLastRow, PrecipFirstRow, _
    PrecipDateColumn, PrecipTimeColumn, g, h, i, j, NumberOfRows, PrecipIncrament, PrecipDifference)

    Dim DateTimeCombined2 As Date           'DateTimeCombined2 is the combined date and time of the current level logger time step being checked
    Dim LevelEndDateTime As Date            'LevelEndDateTime is the last combined date and time in the level logger file
    Dim LevelStartDateTime As Date          'LevelStartDateTime is the first combined date and time in the level logger file
    Dim PrecipDate As Date                  'PrecipDate is the date of the current precipitation value
    Dim PrecipDateTimeCombined As Date      'PrecipDateTimeCombined is the combined date and time of the current precipitation value
    Dim PrecipTime As Date                  'PrecipTime is the time of the current precipitation value

    Dim PrecipRows As Integer               'PrecipRows is the number of rows in the level logger precipitation file

'    Dim PrecipExist As Single               'PrecipExist is the precipitation that has already been recorded in the current level logger time step
'    Dim PrecipNew As Single                 'PrecipNew is the precipitation value that will be written out and consists of the existing precipitation at the current level logger time step plus the precipitation incrament value

    Dim PrecipCell As String                'PrecipCell is the address of the cell that the precipitation value will be written to
    Dim PrecipDateCell As String            'PrecipDateCell is the address of the cell that contains the date of the current precipitation incrament
    Dim PrecipTimeCell As String            'PrecipTimeCell is the address of the cell that contains the time of the current precipitation incrament

    Dim PrecipWorkbook As Workbook          'PrecipWorkbook is the workbook that contains the precipitation data

' Zeroing indicator variables, opening the Precipitation Input file, and calculating the number of rows
'      of data that need to be compared to the level logger date and time

    PrecipDifference = 0
    LevelStartDateTime = ActiveSheet.Range("K2").Value
    LevelEndDateTime = ActiveSheet.Range(CombinedCell).Value
    Workbooks.Open PrecipFile
    Set PrecipWorkbook = ActiveWorkbook
    PrecipRows = PrecipLastRow - PrecipFirstRow + 1

' Finding the row of precipitation data to begin with (assuming precipitation file has a date range that
'      exceeds the Level Logger file)

    Let PrecipDateCell = PrecipDateColumn & PrecipFirstRow
    Let PrecipTimeCell = PrecipTimeColumn & PrecipFirstRow
    PrecipDate = ActiveSheet.Range(PrecipDateCell).Value
    PrecipTime = ActiveSheet.Range(PrecipTimeCell).Value
    PrecipDateTimeCombined = PrecipDate + PrecipTime
    If PrecipDateTimeCombined > LevelStartDateTime Or PrecipDateTimeCombined = LevelStartDateTime Then
        GoTo FindPrecipEnd
    Else
        h = PrecipFirstRow
        For g = 1 To PrecipRows
            h = h + 1
            Let PrecipDateCell = PrecipDateColumn & h
            Let PrecipTimeCell = PrecipTimeColumn & h
            PrecipDate = ActiveSheet.Range(PrecipDateCell).Value
            PrecipTime = ActiveSheet.Range(PrecipTimeCell).Value
            PrecipDateTimeCombined = PrecipDate + PrecipTime
            If PrecipDateTimeCombined > LevelStartDateTime Or PrecipDateTimeCombined = LevelStartDateTime Then
                PrecipFirstRow = h
                GoTo FindPrecipEnd
            End If
        Next g
        MsgBox "Error - The time frame of the Precipitation Logger data file does not appear to coincide " & _
            "with the time frame of the Level Logger data file.  No precipitation data was imported into " & _
            "the Excel Output file."
        GoTo PrecipFileClose
    End If

' Finding the row of precipitation data to end with (assuming precipitation file has a date range that
'      exceeds the Level Logger file)

FindPrecipEnd:
    PrecipRows = PrecipLastRow - PrecipFirstRow + 1
    Let PrecipDateCell = PrecipDateColumn & PrecipLastRow
    Let PrecipTimeCell = PrecipTimeColumn & PrecipLastRow
    PrecipDate = ActiveSheet.Range(PrecipDateCell).Value
    PrecipTime = ActiveSheet.Range(PrecipTimeCell).Value
    PrecipDateTimeCombined = PrecipDate + PrecipTime
    If PrecipDateTimeCombined < LevelEndDateTime Or PrecipDateTimeCombined = LevelEndDateTime Then
        PrecipDifference = Round(LevelEndDateTime - PrecipDateTimeCombined, 3)
        GoTo AddPrecipData
    Else
        h = PrecipLastRow
        For g = 1 To PrecipRows
            h = h - 1
            Let PrecipDateCell = PrecipDateColumn & h
            Let PrecipTimeCell = PrecipTimeColumn & h
            PrecipDate = ActiveSheet.Range(PrecipDateCell).Value
            PrecipTime = ActiveSheet.Range(PrecipTimeCell).Value
            PrecipDateTimeCombined = PrecipDate + PrecipTime
            If PrecipDateTimeCombined < LevelEndDateTime Or PrecipDateTimeCombined = LevelEndDateTime Then
                PrecipLastRow = h
                GoTo AddPrecipData
            End If
        Next g
    End If

' Comparing the date and time in the Precipitation Logger data file to the date and time of the Level Logger
'      data file and adding precipitation to the proper time steps
    
AddPrecipData:
    PrecipRows = PrecipLastRow - PrecipFirstRow + 1
    h = PrecipFirstRow - 1
    j = 1
    For g = 1 To PrecipRows
        PrecipWorkbook.Activate
        h = h + 1
        Let PrecipDateCell = PrecipDateColumn & h
        Let PrecipTimeCell = PrecipTimeColumn & h
        PrecipDate = ActiveSheet.Range(PrecipDateCell).Value
        PrecipTime = ActiveSheet.Range(PrecipTimeCell).Value
        PrecipDateTimeCombined = PrecipDate + PrecipTime
        ConversionWorkbook.Activate
        For i = 1 To NumberOfRows + 1 - j
            j = j + 1
            Let CombinedCell = "K" & j
            DateTimeCombined2 = ActiveSheet.Range(CombinedCell).Value
            If DateTimeCombined2 > PrecipDateTimeCombined Or DateTimeCombined2 = PrecipDateTimeCombined Then
                Let PrecipCell = "J" & j
                PrecipExist = ActiveSheet.Range(PrecipCell).Value
                PrecipNew = Round(PrecipExist + PrecipIncrament, 3)
                ActiveSheet.Range(PrecipCell).Value = PrecipNew
                GoTo PrecipMatch
            End If
        Next i

PrecipMatch:
    j = j - 1
    Next g

' Closing the Precipitation Input file

PrecipFileClose:
    Application.DisplayAlerts = False
    PrecipWorkbook.Activate
    ActiveWorkbook.Close False
    ConversionWorkbook.Activate
    Application.Goto Sheets("Imported Raw Data").Range("A1")
    Application.DisplayAlerts = True
End Sub

Note that, in the example code above, I have commented out the Single dimensions for the precipitation values as I mentioned previously.

Thanks again for your prompt reply!

 

[joeu2004]

I input the precipitation incrament as 0.01 inches. So, you would expect that all of the values recorded for precipitation would be some multiple of that (0.01, 0.02, 0.03, 0.10, 0.25, etc.) depending on how much precipitation fell during that level logger time step. What actually gets recorded in the spreadsheet is 0.00999999977648258 (instead of 0.01), or 0.0299999993294477 (instead of 0.03), etc.
[....]
The variables are all dimensioned as Single (PrecipNew, PrecipExist, and PrecipIncrament).

Change the VBA type to Double.

But continue to round explicitly. I would use WorksheetFunction.Round, not VBA Round. The latter rounds differently ("banker's rounding").

Explanation....

Excel effectively uses type Double (64-bit binary floating-point) to store numbers. Type Single (32-bit binary floating-point) has less precision.

When we convert type Single into type Double, we effectively truncate the binary approximation of decimal fractions.

The following demonstrates the problem.

Sub testit()
    Dim x As Single, y As Double
    x = 12.01
    y = x
    MsgBox Format(x, "0.0000000000000") & _
        vbNewLine & Format(y, "0.0000000000000")
End Sub

The result is 12.0100000000000 (x) and 12.0100002288818 .

 

[joeu2004]

Another engineer in my office just suggested I remove the dimension statements so that the variables are no longer dimensioned as Single. That worked and I now get the expected 0.01, 0.02, 0.10, etc.

Although that does work, it is a poor idea.

First, you should always declare (Dim) variables.

In fact, you should have an Option Explicit statement at the beginning of the VBA module to force you to declare variables. That avoids unnoticed typos from causing you to inadvertently use new uninitialized variables.

Second, when you declare or use a variable without a type, it is implicitly type Variant. That can be useful at times. But generally, using Variant variables is less efficient. And on rare occassion, they can cause "inexplicable" programming misbehaviors.

The implicit type Variant variable seemed to work because VBA assigns type Double to numeric results, unless their type is something else.

So removing the type declaration effectively did what I suggested: it replaced type Single with type Double. However, an explicit type Double is more efficient.

PS: I also notice that you use type Integer. Generally, it is more reliable to use type Long. There is no benefit to use type Integer these days, except if you have very large arrays of values, and you are concerned about memory footprint.

 

[dareman93]

Change the VBA type to Double.

That works as well. After I commented out the Dim statements, I changed them to Double and uncommented them and the program exports what I was expecting. Thanks for your help!

 

[dareman93]

Although that does work, it is a poor idea.

First, you should always declare (Dim) variables.

I agree whole-heartedly. I only commented out the Dim statement to see if that was the problem. I'm 'old school' when it comes to programming - I use tons of comments and dimension everything. If somebody has to come in and reverse engineer my code, I want to make it as easy as possible on them by leaving them a trail of breadcrumbs as to why/how I did things.

PS: I also notice that you use type Integer. Generally, it is more reliable to use type Long. There is no benefit to use type Integer these days, except if you have very large arrays of values, and you are concerned about memory footprint.

These are relatively large data sets (logging data every 5 minutes for upwards of 4 to 6 months).

Thank you for the reply and your assistance. It is greatly appreciated!

 

[joeu2004]

PS: I also notice that you use type Integer. Generally, it is more reliable to use type Long. There is no benefit to use type Integer these days, except if you have very large arrays of values, and you are concerned about memory footprint.

These are relatively large data sets (logging data every 5 minutes for upwards of 4 to 6 months).

The "memory footprint" I referred to is "very large arrays" of integers. I do not see that in your posted code. What I do see is:

Dim PrecipRows As Integer    'PrecipRows is the number of rows in the level logger precipitation file

This will cause a VBA runtime error if you might have more than 32,767 rows in that file. Note that 32,767 entries every 5 min covers just under 114 days, a far cry from "4 to 6 months" (up to 52,704 entries).

Even if PrecipRows will never be that large, the point is: it is prudent to get out of the habit of using type Integer, always favoring type Long by default.

(Some exceptions: the memory footprint issue note previously; and calling procedures with ByRef type Integer parameters.)

That way, you are less likely to have integer arithmetic errors in future programming projects, at least errors due to integer size.

-----

PS....

When we convert type Single into type Double, we effectively truncate the binary approximation of decimal fractions. The following demonstrates the problem.

Sub testit()
    Dim x As Single, y As Double
    x = 12.01
    y = x
    MsgBox Format(x, "0.0000000000000") & _
        vbNewLine & Format(y, "0.0000000000000")
End Sub

The result is 12.0100000000000 (x) and 12.0100002288818 .

In effort to keep things simple, the details above might be misleading.

In fact, the type Single representation of 12.01 is about 12.0100002288818, just like the type Double representation. Actually, it is exactly 12.0100002288818359375.

The type Single representation appears to be exactly 12.01 because VBA formats only up to 7 significant digits for type Single expressions.

I included additional decimal places just to be consistent with the type Double representation. VBA formats (only) up to 15 significant digits for type Double.

But the point was: if we assign 12.01 to a type Double variable in the first place, there are more binary digits with which to estimate the decimal fraction.

So the type Double representation will appear to be exactly 12.01 when formatted with up to 15 significant digits.

In fact, the type Double representation of 12.01 is exacty 12.0099999999999,997868371792719699442386627197265625. The comma demarcates 15 significant digits to the left.

This is illustrated by the following more-complete code.

Sub testit()
    Dim x As Single, y As Double, z As Double
    x = 12.01
    y = x
    z = 12.01
    MsgBox "x: " & Format(x, "0.0000000000000") & _
        vbNewLine & "y: " & Format(y, "0.0000000000000") & _
        vbNewLine & "z: " & Format(z, "0.0000000000000")
End Sub

The result is:

x: 12.0100000000000
y: 12.0100002288818
z: 12.0100000000000

 

[Rick Rothstein]

I would use WorksheetFunction.Round, not VBA Round. The latter rounds differently ("banker's rounding").

You could also use the Format function... it is the only function in all of VB that use "normal" rounding... every other function, statement or operator that performs rounding uses "Banker's Rounding". For example, ...

MsgBox Round(1.25, 1) & " - " & Format(1.25, "0.0")

 

[vlady]

I'm not in vba i just found something like this

N is the result of your = Round (PrecipExist + PrecipIncrament, 2)


thenumber = Str(N)
I = InStr(1, thenumber, ".")
N = Val(Left(thenumber, X - 1))