Can excel calculate?
 

On Thu, 24 Jul 2003 01:42:40 -0700, "Karl Itschen"
wrote:

Strange strange... look at this simple excel-VBA code

Sub test()
Dim tempRound As Double
Dim tempInt, tempDec As Double
Dim number As Double
number = 64.1

tempRound = Round(number, 1)
tempInt = Int(tempRound)
tempDec = (10 * (tempRound - tempInt))

Debug.Print "tempRound = " & tempRound
Debug.Print "tempInt = " & tempInt
Debug.Print "tempDec = " & tempDec

End Sub

Ok this code is supposed to have the integer part
of "number" in the "tempInt" variable (ie tempInt=64) and
the decimal part rounded to the first decimal value in
the "tempDec" variable (ie tempDec=1).
BUT it outputs:

tempRound = 64.1
tempInt = 64
tempDec = 0.999999999999943

Hmmm anything wrong here perhaps!!!!
Any comment?!?
Karl

As others have pointed out, the problem is in the 15 digit precision and binary
storage that the specifications call for in storing Double precision numbers.

However, you can get around this limitation, depending on the magnitude of the
number, by using the Decimal data type. For example,

==================
Sub test()
Dim tempRound As Double
Dim tempInt As Double, tempDec As Variant
Dim number As Double
number = 64.1

tempRound = Round(number, 1)
tempInt = Int(tempRound)
tempDec = (10 * (CDec(tempRound) - CDec(tempInt)))

Debug.Print "tempRound = " & tempRound
Debug.Print "tempInt = " & tempInt
Debug.Print "tempDec = " & tempDec

End Sub
=====================
tempRound = 64.1
tempInt = 64
tempDec = 1
===================

See HELP for details on acceptable magnitudes.



--ron

Can excel calculate?
 

Just to mention Excel's other method to do a limited amout of arbitrary
precision is the use of Currency.
Depending on your range of numbers, this might work...

Sub Demo()
Dim tempRound As Currency
Dim tempInt As Currency
Dim tempDec As Currency
Dim number As Currency

number = 64.1

tempRound = Round(number, 1)
tempInt = Int(tempRound)
tempDec = 10 * (tempRound - tempInt)

Debug.Print "tempRound = " & tempRound
Debug.Print "tempInt = " & tempInt
Debug.Print "tempDec = " & tempDec
End Sub

tempRound = 64.1
tempInt = 64
tempDec = 1


I like Ron's idea. You can also have Excel automatically work at the higher
precision if you want with the following idea...

Sub Demo2()
Dim tempRound
Dim tempInt
Dim tempDec
Dim number

number = CDec(64.1)

tempRound = Round(number, 1)
tempInt = Int(tempRound)
tempDec = 10 * (tempRound - tempInt)

Debug.Print "tempRound = " & tempRound
Debug.Print "tempInt = " & tempInt
Debug.Print "tempDec = " & tempDec
End Sub

tempRound = 64.1
tempInt = 64
tempDec = 1

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Karl Itschen" wrote in message
...
Cool thanks, that works fine!
Karl
-----Original Message-----
On Thu, 24 Jul 2003 01:42:40 -0700, "Karl Itschen"

wrote:

Strange strange... look at this simple excel-VBA code

Sub test()
Dim tempRound As Double
Dim tempInt, tempDec As Double
Dim number As Double
number = 64.1

tempRound = Round(number, 1)
tempInt = Int(tempRound)
tempDec = (10 * (tempRound - tempInt))

Debug.Print "tempRound = " & tempRound
Debug.Print "tempInt = " & tempInt
Debug.Print "tempDec = " & tempDec

End Sub

Ok this code is supposed to have the integer part
of "number" in the "tempInt" variable (ie tempInt=64)
and
the decimal part rounded to the first decimal value in
the "tempDec" variable (ie tempDec=1).
BUT it outputs:

tempRound = 64.1
tempInt = 64
tempDec = 0.999999999999943

Hmmm anything wrong here perhaps!!!!
Any comment?!?
Karl

As others have pointed out, the problem is in the 15
digit precision and binary
storage that the specifications call for in storing
Double precision numbers.

However, you can get around this limitation, depending on
the magnitude of the
number, by using the Decimal data type. For example,

==================
Sub test()
Dim tempRound As Double
Dim tempInt As Double, tempDec As Variant
Dim number As Double
number = 64.1

tempRound = Round(number, 1)
tempInt = Int(tempRound)
tempDec = (10 * (CDec(tempRound) - CDec(tempInt)))

Debug.Print "tempRound = " & tempRound
Debug.Print "tempInt = " & tempInt
Debug.Print "tempDec = " & tempDec

End Sub
=====================
tempRound = 64.1
tempInt = 64
tempDec = 1
===================

See HELP for details on acceptable magnitudes.



--ron
.