Banker rounding when using Currency is corrupt. See example. Can this be true?

[Jamualson]

Very strange, I expected banker rounding, but this seems random. What can be the cause? See picture. Thank you very much

Sub Test()

Dim c1 As Currency
Dim c2 As Currency
Dim c3 As Currency
Dim c4 As Currency
Dim c5 As Currency
Dim c6 As Currency
Dim c7 As Currency
Dim c8 As Currency
Dim c9 As Currency


c1 = 0.22225: Debug.Print c1
c2 = 1.22225: Debug.Print c2
c3 = 2.22225: Debug.Print c3
c4 = 3.22225: Debug.Print c4
c5 = 4.22225: Debug.Print c5
c6 = 5.22225: Debug.Print c6
c7 = 6.22225: Debug.Print c7
c8 = 8.22225: Debug.Print c8
c9 = 8.22225: Debug.Print c9


End Sub

 

[mikerickson]

Numbers are stored as binary values. Its not strange that the last digit of a decimal is rounded differently that one would expect.

 

[Sulprobil]

[Deleted - the 4 digits to the right are known, just the rounding is surprising ]

 

[Marc L]

Sub Demo1()
    For N% = 0 To 9
        C@ = Application.RoundDown(N + 0.22225, 4)
        Debug.Print C
    Next
End Sub

 

[Jamualson]

Numbers are stored as binary values. Its not strange that the last digit of a decimal is rounded differently that one would expect.

Thank you very much. Banker rounding confused me a little, because I expected Banker rounding here as well. What can be the cause that banker rounding doesnt work here, but works at other times?
I thought banker rounding is universal.

 

[joeu2004]

What can be the cause that banker rounding doesnt work here, but works at other times?

In part, the answer is: apparently, VBA does not do simple banker's rounding when converting Double to Currency, which is rounded to 4 decimal places.

The following demonstrates that both banker's and normal rounding are consistent.

Sub testCur()
Dim i As Variant, d As Double
Dim c1 As Currency, c2 As Currency, c3 As Currency
Dim c4i As Long, c4f As Long
Debug.Print "double", "implicit", "banker's", "normal", "sim cur"
For Each i In Array(0, 1, 2, 3, 4, 5, 6, 8, 9)
    d = i + 0.22225
    c1 = d                      ' implicit rounding
    c2 = Round(d, 4)            ' banker's rounding
    c3 = Format(d, "0.0000")    ' normal rounding
    c4i = Int(d)                ' simulate type Currency
    c4f = (d - Int(d)) * 10000
    Debug.Print d, c1, c2, c3, c4i & "." & c4f
Next
MsgBox "done"
End Sub

Result:

double        implicit      banker's      normal        sim cur
 0.22225       0.2223        0.2222        0.2223       0.2223
 1.22225       1.2223        1.2222        1.2223       1.2223
 2.22225       2.2222        2.2222        2.2223       2.2222
 3.22225       3.2222        3.2222        3.2223       3.2222
 4.22225       4.2222        4.2222        4.2223       4.2222
 5.22225       5.2222        5.2222        5.2223       5.2222
 6.22225       6.2222        6.2222        6.2223       6.2222
 8.22225       8.2223        8.2222        8.2223       8.2223
 9.22225       9.2223        9.2222        9.2223       9.2223

Note that the "banker's" and "normal" columns have consistent and expected results [1].

Also note that the "implicit" and "sim cur" columns have consistent results.

For the "sim cur" column, I speculate how that the integer part and the 4-digit fractional part scaled by 10000 are converted separately from the original type Double, instead of the type Double after banker's rounding to 4 decimal places.

The bottom line is: for consistent results [1], explicitly banker's round the right-hand side with VBA Round, because apparently VBA does not (sigh).

But I believe the following is a more-reliable way to do the banker's rounding:

c2 = Round(CDec(d), 4)

-----

[1] The conversion from type Double (d - Int(d))*10000 to type Long should use banker's rounding.

So we return to the question: why the inconsistency results for 0.22225, 1.22225, 8.22225 and 9.22225?

In part, the answer is: most decimal fractions (0.22225) cannot be represented exactly in 64-bit binary floating-point. And the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number.

I also believe that the banker's rounding is based on the internal 80-bit binary floating-point representation of type Double calculation (in Intel-compatible CPUs).

For 8.22225 and 9.22225, we can see the effect by debug.printing (d - Int(d)) * 10000). The result appears to be 2222.50000000001 instead of 2222.5. With banker's rounding, that rounds up to 2223 because the fractional part is not 0.5.

We cannot see this with 0.22225 and 1.22225, in part because Excel and VBA format only the first 15 significant digits (rounded).

I could explain this in more detail. But it is TMI for most people.

I believe the following is a more-reliable way to convert the 4-digit fractional part scaled by 10000 to type Long:

c4f = Round((CDec(d) - Int(d)) * 10000, 0)

 

[joeu2004]

Errata (too late to edit)....

Dim c4i As Long, c4f As Long
[....]
c4i = Int(d) ' simulate type Currency
c4f = (d - Int(d)) * 10000
[....]
The bottom line is: for consistent results [1], explicitly banker's round the right-hand side with VBA Round, because apparently VBA does not (sigh).
[.... using ....]
c2 = Round(CDec(d), 4)

There are some technical mistakes in my previous explanation. But the point is the "bottom line" above.

 

[Jamualson]

In part, the answer is: apparently, VBA does not do simple banker's rounding when converting Double to Currency, which is rounded to 4 decimal places.

The following demonstrates that both banker's and normal rounding are consistent.

Sub testCur()
Dim i As Variant, d As Double
Dim c1 As Currency, c2 As Currency, c3 As Currency
Dim c4i As Long, c4f As Long
Debug.Print "double", "implicit", "banker's", "normal", "sim cur"
For Each i In Array(0, 1, 2, 3, 4, 5, 6, 8, 9)
    d = i + 0.22225
    c1 = d                      ' implicit rounding
    c2 = Round(d, 4)            ' banker's rounding
    c3 = Format(d, "0.0000")    ' normal rounding
    c4i = Int(d)                ' simulate type Currency
    c4f = (d - Int(d)) * 10000
    Debug.Print d, c1, c2, c3, c4i & "." & c4f
Next
MsgBox "done"
End Sub

Result:

double        implicit      banker's      normal        sim cur
 0.22225       0.2223        0.2222        0.2223       0.2223
 1.22225       1.2223        1.2222        1.2223       1.2223
 2.22225       2.2222        2.2222        2.2223       2.2222
 3.22225       3.2222        3.2222        3.2223       3.2222
 4.22225       4.2222        4.2222        4.2223       4.2222
 5.22225       5.2222        5.2222        5.2223       5.2222
 6.22225       6.2222        6.2222        6.2223       6.2222
 8.22225       8.2223        8.2222        8.2223       8.2223
 9.22225       9.2223        9.2222        9.2223       9.2223

Note that the "banker's" and "normal" columns have consistent and expected results [1].

Also note that the "implicit" and "sim cur" columns have consistent results.

For the "sim cur" column, I speculate how that the integer part and the 4-digit fractional part scaled by 10000 are converted separately from the original type Double, instead of the type Double after banker's rounding to 4 decimal places.

The bottom line is: for consistent results [1], explicitly banker's round the right-hand side with VBA Round, because apparently VBA does not (sigh).

But I believe the following is a more-reliable way to do the banker's rounding:

c2 = Round(CDec(d), 4)

-----

[1] The conversion from type Double (d - Int(d))*10000 to type Long should use banker's rounding.

So we return to the question: why the inconsistency results for 0.22225, 1.22225, 8.22225 and 9.22225?

In part, the answer is: most decimal fractions (0.22225) cannot be represented exactly in 64-bit binary floating-point. And the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number.

I also believe that the banker's rounding is based on the internal 80-bit binary floating-point representation of type Double calculation (in Intel-compatible CPUs).

For 8.22225 and 9.22225, we can see the effect by debug.printing (d - Int(d)) * 10000). The result appears to be 2222.50000000001 instead of 2222.5. With banker's rounding, that rounds up to 2223 because the fractional part is not 0.5.

We cannot see this with 0.22225 and 1.22225, in part because Excel and VBA format only the first 15 significant digits (rounded).

I could explain this in more detail. But it is TMI for most people.

I believe the following is a more-reliable way to convert the 4-digit fractional part scaled by 10000 to type Long:

c4f = Round((CDec(d) - Int(d)) * 10000, 0)

Wow, that is a quality answer. Thank you very much!!!