Bug with the function Round !

[Tschuß]

Hi all,

I was making an exercise for my students when I have discovered a bug
when I use the function Round.
In the column E, you have this data
Last quote
32,13
31,46
31,67
31,17
30,12
31,86
31,96
33,28
33,35
33,38
33,08
33,34
32,38
31,98
32,09
32,73
33,05
32,82
33,08

My program is very simple. I calculate the average of the first 20
cells and I write the result in Cells(21,6)
Why the round function doesn't work in the first Sub (the result is
32,4099998474121) and works in the second one ?
Thanks for any idea.

Sub Step_1()
Dim Temp_Sum As Single
Dim index_loop As Integer

For index_loop = 1 To 20
Temp_Sum = Temp_Sum + Cells(1 + index_loop, 5)
Next
Cells(21, 6) = Round(Temp_Sum / 20, 2)

End Sub

(the cell(1,6) has the value 20)
Sub Step_2()
Dim Temp_Sum As Single
Dim index_loop As Integer

For index_loop = 1 To Cells(1, 6)
Temp_Sum = Temp_Sum + Cells(1 + index_loop, 5)
Next
Cells(1 + Cells(1, 6), 6) = Round(Temp_Sum / Cells(1, 6), 2)

End Sub

 

[Monarch]

Cells(1,6) is Variant/Double
20 is integer
Temp_Sum is single...

if you try Cells(22, 6) = Round(Temp_Sum / 20#, 2)
it will work

 

[Monarch]

Also
this does not round
Cells(22, 6) = Round(CSng(Temp_Sum / 20), 2)
but this does it
Cells(22, 6) = Round(CDbl(Temp_Sum / 20), 2)

so explanation is that single data does not have enough precision

 

[Gary''s Student]

You only list 19 values in your post. Do you want the 20th value to be zero
or blank??

 

[Ron Rosenfeld]

Hi all,

I was making an exercise for my students when I have discovered a bug
when I use the function Round.
In the column E, you have this data
Last quote
32,13
31,46
31,67
31,17
30,12
31,86
31,96
33,28
33,35
33,38
33,08
33,34
32,38
31,98
32,09
32,73
33,05
32,82
33,08

My program is very simple. I calculate the average of the first 20
cells and I write the result in Cells(21,6)
Why the round function doesn't work in the first Sub (the result is
32,4099998474121) and works in the second one ?
Thanks for any idea.

Sub Step_1()
Dim Temp_Sum As Single
Dim index_loop As Integer

For index_loop = 1 To 20
Temp_Sum = Temp_Sum + Cells(1 + index_loop, 5)
Next
Cells(21, 6) = Round(Temp_Sum / 20, 2)

End Sub

(the cell(1,6) has the value 20)
Sub Step_2()
Dim Temp_Sum As Single
Dim index_loop As Integer

For index_loop = 1 To Cells(1, 6)
Temp_Sum = Temp_Sum + Cells(1 + index_loop, 5)
Next
Cells(1 + Cells(1, 6), 6) = Round(Temp_Sum / Cells(1, 6), 2)

End Sub

Your first Sub seems to work properly, returning 32.40999984741210
provided that you put a 33.27 into E21 (the 20th cell in the series). As
written above, you only have 19 values.
--ron

 

[Skiffle]

"Tschuß",

As Monarch pointed out, a variable dimensioned as Single does not have the
necessary precision for the task at hand.

In general, it is rarely necessary to dimension any variable as Single or
Integer. Those variables would better be served as Double or Long,
respectively.

Single or Integer variables were used more frequently when conservation of
memory was a concern. Single or Integer variables require less internal
memory than their Double or Long counterparts. Since memory is so
inexpensive today and usually in abundance, conserving it is no longer
considered necessary. Therefore, Double or Long is more common practice in
place of Single or Integer.

 

[Joe User]

Why the round function doesn't work in the first
Sub (the result is 32,4099998474121) and works in
the second one ?

Your explanation is incomplete (what does "work" mean?); but so are the
responses posted so far, IMHO. Let me fill in the blanks. (Note: I use
period instead of comma as the radix point.)

First, it appears that your question is: why does the first macro result in
32.4099998474121, whereas the second macro results in 32.4100000000000 as
you expect?

Your observation is correct, assuming that the missing 20th data value is
33.27.

As others have noted, the root cause of the problem is your use of Single
instead of Double. The problem arises when your Single expression is
converted to Double, the precision used for numbers in Excel cells.

In the statement `Cells(21, 6) = Round(Temp_Sum / 20, 2)` in the Step_1
macro, the result of Round() has Single precision because Temp_Sum is type
Single and 20 is an integer. The Single precision right-hand side is then
converted to Double, conceptually by appending zero bits on the right. This
is an imperfect conversion, as I will demonstrate below.

(Note: A Single value is represented using 32-bit floating point, whereas a
Double is represented using 64-bit floating point.)

In contrast, in the statement `Cells(1 + Cells(1, 6), 6) = Round(Temp_Sum /
Cells(1, 6), 2)` n the Step_2 macro, the result of Round() has Double
precision because Temp_Sum is converted to Double since Cells(1,6) has
Double precision. Although the Temp_Sum conversion is also imperfect, the
Round() precision of 2 is sufficiently small to mask the imperfect result of
Temp_Sum / Cells(1, 6).

In order to understand the imperfect conversion of Single to Double, it is
necessary to look at the binary representation of numbers. Most numbers
with fractional digits cannot be represented exactly because the floating
point representation is the sum of a finite number of consecutive powers of
2 -- 53 for Double, 24 for Single.

Consequently, the Double value 32.41 is represented by &h4040347A,E147AE14.
But when the Single value 32.41 is converted to Double, it is represented by
&h4040347A,E0000000.

Obviously, these are two different representations, representing two
different numbers.

In summary, the problem is not with Round(), but with the necessarily
imperfect hardware conversion of Single to Double.

You could avoid the imperfect conversion by first converting the Single
expression to text. For example, Format(Round(Temp_Sum / 20, 2), "0.00")
or, more simply, Format(Temp_Sum / 20, "0.00").

Of course, the better solution is simply to use type Double instead of
Single.

There is little benefit to using Single these days. Single does require
less memory; but that should be significant only when you are dealing huge
amounts of data, e.g. billions of data values. In most modern computers,
there is no performance benefit to using Single.

It should be noted that in both macros, the statement `Temp_Sum = Temp_Sum +
Cells(1 + index_loop, 5)` repeatedly causes infinitesimal imperfections in
the computation when the Double expression on the right-hand side is rounded
to Single precision for assignment to the left-hand side.

All the more reason to use type Double instead of Single consistently.


----- original message -----

 

[Tschuß]

Thanks all for your answer.

First, I apology because it's true that I forgot the last value ;
33.21
@Monarch : Could you explain me the contribution of the symbol # after
20 ?
@Joe User : Thanks a lot for your explanation (expect for the
hexadecimal part). But I understand that it's better to use Double in
any case.

 

[Monarch]

Sign Language ..
Type Declaration chararcters...
% - integer
& - Long
# - Double
! - single
@ - Currency

short way to convert values...
20! is single, 20# is double, 20 or 20% is integer

run this macro

Sub test()
aInt = TypeName(20)
aDbl = TypeName(20#)
aSng = TypeName(20!)
aCur = TypeName(20@)
aLng = TypeName(20&)
MsgBox aInt & " " & aDbl & " " & aSng & " " & aCur & " " & aLng
End Sub