Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can excel calculate?
Binary rounding error. Because data is stored in binary
code in a computer there are certain decimal values that don't quite work out properly when you perform mathematical operations on them. Binary is inherently a bad numbering system because it can really only handle integer values well. In fact, it's so bad that: 64.1-64=0.999999999999943 64.2-64=0.200000000000003 64.3-64=0.299999999999997 64.4-64=0.400000000000006 64.5-64=0.5 (Hey, this one works!) 64.6-64=0.599999999999994 64.7-64=0.700000000000003 64.8-64=0.799999999999997 64.9-64=0.900000000000006 -----Original Message----- 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 . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can excel calculate?
You're not serious are you? You mean, as I know the
difference will be 0.1, I should round the wrong result from the calculation so that it fits 0.1 !!!??? Btw, I'm using this value as a seed for a filename, and I'm happier if my file is called: 64_1.txt and not 64_0.999999999999946.txt ;-) I guess you have to be VERY careful with numbers in VB... Karl oh, and C++ does it right! (see my answer to your 1st post) -----Original Message----- How to write 64.1-64 and get EXACTLY 0.1 ? (that must be possible in VB or not?) Why? The deviation we are talking about here is of the same order as 1 cm compared to the distance between the earth and the sun. If you do not want to see the mess caused by the calculations, round the result to the desired number of decimals. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can excel calculate?
Final remark:
That C++ does it wrong in this case does not mean that the outcome of a floating point operation is, in general, exact. It is not. In no compiler. Ever. Anyway, there are simpler ways of replacing the decimal . by an underscore. As about the ignoring rounding errors (or making them fit, as you call it): suppose we were dealing with money, why would rounding the results to 2 decimals be so bad? I don't know about you, but I can spare 5.7e-12 cents any time. -----Original Message----- You're not serious are you? You mean, as I know the difference will be 0.1, I should round the wrong result from the calculation so that it fits 0.1 !!!??? Btw, I'm using this value as a seed for a filename, and I'm happier if my file is called: 64_1.txt and not 64_0.999999999999946.txt ;-) I guess you have to be VERY careful with numbers in VB... Karl oh, and C++ does it right! (see my answer to your 1st post) -----Original Message----- How to write 64.1-64 and get EXACTLY 0.1 ? (that must be possible in VB or not?) Why? The deviation we are talking about here is of the same order as 1 cm compared to the distance between the earth and the sun. If you do not want to see the mess caused by the calculations, round the result to the desired number of decimals. . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can excel calculate?
Thanks for your answers anyway.
Best regards Karl -----Original Message----- Final remark: That C++ does it wrong in this case does not mean that the outcome of a floating point operation is, in general, exact. It is not. In no compiler. Ever. Anyway, there are simpler ways of replacing the decimal . by an underscore. As about the ignoring rounding errors (or making them fit, as you call it): suppose we were dealing with money, why would rounding the results to 2 decimals be so bad? I don't know about you, but I can spare 5.7e-12 cents any time. -----Original Message----- You're not serious are you? You mean, as I know the difference will be 0.1, I should round the wrong result from the calculation so that it fits 0.1 !!!??? Btw, I'm using this value as a seed for a filename, and I'm happier if my file is called: 64_1.txt and not 64_0.999999999999946.txt ;-) I guess you have to be VERY careful with numbers in VB... Karl oh, and C++ does it right! (see my answer to your 1st post) -----Original Message----- How to write 64.1-64 and get EXACTLY 0.1 ? (that must be possible in VB or not?) Why? The deviation we are talking about here is of the same order as 1 cm compared to the distance between the earth and the sun. If you do not want to see the mess caused by the calculations, round the result to the desired number of decimals. . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I calculate Ker and Kei using Excel | Excel Worksheet Functions | |||
Excel won't calculate | Excel Discussion (Misc queries) | |||
How to get Excel to calculate | Excel Discussion (Misc queries) | |||
how do i calculate in excel % Available | Excel Discussion (Misc queries) | |||
how can i calculate uk apr with excel | Excel Worksheet Functions |