Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type of variables and their effect on result...
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type of variables and their effect on result...
On Fri, 29 Dec 2006 18:25:27 -0500, "MichDenis" wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type of variables and their effect on result...
On Fri, 29 Dec 2006 23:13:26 -0500, "MichDenis" wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type of variables and their effect on result...
"Ron Rosenfeld" wrote:
.... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type of variables and their effect on result...
On Sat, 30 Dec 2006 12:41:01 -0800, Jerry W. Lewis
wrote: "Ron Rosenfeld" wrote: ... 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.7265999999999976921571942511945962905883789062 5 When you multiply by 10000, you get 377265.999999999976921571942511945962905883789062 5 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type of variables and their effect on result...
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to get a result from 2 variables | Excel Worksheet Functions | |||
3 LookUp Variables One Result | Excel Worksheet Functions | |||
two variables to get result | Excel Worksheet Functions | |||
TWO VARIABLES - ONE RESULT | Excel Discussion (Misc queries) | |||
Enum type variables | Excel Programming |