Type of variables and their effect on result...

[MichDenis]

Hello,

3 ways to make a simple operation (same)

My question : Why the result is not the same ? any explination ?

1 - )
X = Int(37.7266 * 10000)
Result = 377265

2 - )
A = 37.7266
B = 10000
X = Int(A * b)
Result = 377266

3 - )
Suppose we use define the type of these variables ::
Dim x As Long
Dim A As Double, B As Integer
A = 37.7266
B = 10000
x = Int(A * B)
Result = 377265

Thanks in advance for your time and your collaboration.

 

[Ron Rosenfeld]

Hello,

3 ways to make a simple operation (same)

My question : Why the result is not the same ? any explination ?

1 - )
X = Int(37.7266 * 10000)
Result = 377265

2 - )
A = 37.7266
B = 10000
X = Int(A * b)
Result = 377266

3 - )
Suppose we use define the type of these variables ::
Dim x As Long
Dim A As Double, B As Integer
A = 37.7266
B = 10000
x = Int(A * B)
Result = 377265

Thanks in advance for your time and your collaboration.

It has to do with the inherent inaccuracies in the IEEE standard for double
precision floating point numbers.

The number 37.7266 cannot be expressed accurately as a binary number. It's
actually the equivalent of something like 37.726599..... so multiplied by 10000
will be 377265.99... and the INT function will return 377265.

However, when you define A as a variant type, I believe the precision
increases, so you wind up with the "correct" answer.

http://www.cpearson.com/excel/rounding.htm discusses rounding errors in Excel.
The concept is equally applicable to VB.




--ron

 

[MichDenis]

Thanks for this explanation. I already knew it !

If your suggestion is true, Why do we get different results
simply by using and defining or not the type of the variables as
suggested by my question ?

More than that, if you put this formula in a cell the
result is good : = Int(37.7266 * 10000)
= 377266

In a VBA window, X = Int(37.7266 * 10000)
X = 377265

Is the binary systems of numbers compute differently in each case
based on the presentation of the formula ?

If anyone has a supplement of information, I would appreciate.

Thanks for your collaboration.










"Ron Rosenfeld" <[email protected]> a écrit dans le message de (e-mail address removed)...

Hello,

3 ways to make a simple operation (same)

My question : Why the result is not the same ? any explination ?

1 - )
X = Int(37.7266 * 10000)
Result = 377265

2 - )
A = 37.7266
B = 10000
X = Int(A * b)
Result = 377266

3 - )
Suppose we use define the type of these variables ::
Dim x As Long
Dim A As Double, B As Integer
A = 37.7266
B = 10000
x = Int(A * B)
Result = 377265

Thanks in advance for your time and your collaboration.

It has to do with the inherent inaccuracies in the IEEE standard for double
precision floating point numbers.

The number 37.7266 cannot be expressed accurately as a binary number. It's
actually the equivalent of something like 37.726599..... so multiplied by 10000
will be 377265.99... and the INT function will return 377265.

However, when you define A as a variant type, I believe the precision
increases, so you wind up with the "correct" answer.

http://www.cpearson.com/excel/rounding.htm discusses rounding errors in Excel.
The concept is equally applicable to VB.




--ron

 

[Ron Rosenfeld]

Thanks for this explanation. I already knew it !

If your suggestion is true, Why do we get different results
simply by using and defining or not the type of the variables as
suggested by my question ?

More than that, if you put this formula in a cell the
result is good : = Int(37.7266 * 10000)
= 377266

In a VBA window, X = Int(37.7266 * 10000)
X = 377265

Is the binary systems of numbers compute differently in each case
based on the presentation of the formula ?

If anyone has a supplement of information, I would appreciate.

Thanks for your collaboration.

Hopefully someone like Jerry Lewis will notice this thread and give some
definitive information.

Although binary computation should work the same all over, the degree of
precision, and some assumptions made by the programs (Excel vs VBA) are
different.

I have read that Excel makes some assumptions to try to minimize the effects of
binary "inexactness".

In VB, the degree of precision can vary, depending on variable types. An
"undefined" variable type will be defined as a variant, and will retain the
data type of the entered value, unless ... (from HELP):

Generally, numeric Variant data is maintained in its original data type within
the Variant. For example, if you assign an Integer to a Variant, subsequent
operations treat the Variant as an Integer. However, if an arithmetic operation
is performed on a Variant containing a Byte, an Integer, a Long, or a Single,
and the result exceeds the normal range for the original data type, the result
is promoted within the Variant to the next larger data type. A Byte is promoted
to an Integer, an Integer is promoted to a Long, and a Long and a Single are
promoted to a Double. An error occurs when Variant variables containing
Currency, Decimal, and Double values exceed their respective ranges.

Sorry I don't have any more than this very superficial explanation.


--ron

 

[Guest]

:
....

However, when you define A as a variant type, I believe the precision
increases, so you wind up with the "correct" answer.

Actually, the opposite is true, 4-function arithmetic that involves only
constants and explicitly declared doubles is done in extended precision (10
bytes), where it is only done in double precision (8 bytes) with variants.
It is an accident of this particular calculation that the lower precision
calculation resulting from using variants was more in keeping with the OP's
intent.

It has to do with the inherent inaccuracies in the IEEE standard for double
precision floating point numbers.

The number 37.7266 cannot be expressed accurately as a binary number. It's
actually the equivalent of something like 37.726599..... so multiplied by 10000
will be 377265.99... and the INT function will return 377265.

Exactly right. The IEEE double precision binary representation for 37.7266
has the decimal value of
37.72659999999999769215719425119459629058837890625
When you multiply by 10000, you get
377265.9999999999769215719425119459629058837890625
in extended precision, which is approximated by 377266 in double precision.

Thus
Dim A As Variant, B As Variant
A = 37.7266
B = 10000
X = Int(A * B)
returns 377266 because the Int() function received a double precision value,
whereas the other versions returned 3772655 because the Int() function
received an extended precision value.

There are various ways to insure that Int() receives a double precision
value, including
X = Int(CDbl(A * B))
and
X = A * B
X = Int(X)
but the form least likely to give binary surprises is
X = Int(CDbl(CStr(A * B)))
since it works with no more figures than Excel will display.

Jerry

 

[Ron Rosenfeld]

:
...

Actually, the opposite is true, 4-function arithmetic that involves only
constants and explicitly declared doubles is done in extended precision (10
bytes), where it is only done in double precision (8 bytes) with variants.
It is an accident of this particular calculation that the lower precision
calculation resulting from using variants was more in keeping with the OP's
intent.


Exactly right. The IEEE double precision binary representation for 37.7266
has the decimal value of
37.72659999999999769215719425119459629058837890625
When you multiply by 10000, you get
377265.9999999999769215719425119459629058837890625
in extended precision, which is approximated by 377266 in double precision.

Thus
Dim A As Variant, B As Variant
A = 37.7266
B = 10000
X = Int(A * B)
returns 377266 because the Int() function received a double precision value,
whereas the other versions returned 3772655 because the Int() function
received an extended precision value.

There are various ways to insure that Int() receives a double precision
value, including
X = Int(CDbl(A * B))
and
X = A * B
X = Int(X)
but the form least likely to give binary surprises is
X = Int(CDbl(CStr(A * B)))
since it works with no more figures than Excel will display.

Jerry

Thanks for that more accurate and precise <g> and coherent description.


--ron

 

[MichDenis]

Thanks to both of you, Ron et Jerry for all these explanations.




"Ron Rosenfeld" <[email protected]> a écrit dans le message de (e-mail address removed)...

:
...

Actually, the opposite is true, 4-function arithmetic that involves only
constants and explicitly declared doubles is done in extended precision (10
bytes), where it is only done in double precision (8 bytes) with variants.
It is an accident of this particular calculation that the lower precision
calculation resulting from using variants was more in keeping with the OP's
intent.


Exactly right. The IEEE double precision binary representation for 37.7266
has the decimal value of
37.72659999999999769215719425119459629058837890625
When you multiply by 10000, you get
377265.9999999999769215719425119459629058837890625
in extended precision, which is approximated by 377266 in double precision.

Thus
Dim A As Variant, B As Variant
A = 37.7266
B = 10000
X = Int(A * B)
returns 377266 because the Int() function received a double precision value,
whereas the other versions returned 3772655 because the Int() function
received an extended precision value.

There are various ways to insure that Int() receives a double precision
value, including
X = Int(CDbl(A * B))
and
X = A * B
X = Int(X)
but the form least likely to give binary surprises is
X = Int(CDbl(CStr(A * B)))
since it works with no more figures than Excel will display.

Jerry

Thanks for that more accurate and precise <g> and coherent description.


--ron