Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to calculate e^2 in excel | Excel Discussion (Misc queries) | |||
How can I calculate Ker and Kei using Excel | Excel Worksheet Functions | |||
calculate age in excel so I can add it | Excel Discussion (Misc queries) | |||
how can i calculate uk apr with excel | Excel Worksheet Functions | |||
Can excel calculate? | Excel Programming |