accuracy of numbers question #2

[BluesMatt]

Consider the following small program. Run the program and view the contents of the Immediate Window. Where does 3.5527136788005E-14 come from??


Option Explicit

Dim text1 As String
Dim text2 As String


Private Sub Form_Load()
text1 = "4.9"
text2 = "4.9"
Debug.Print (Val(text1) * 100)
Debug.Print (Int(Val(text2) * 100))
Debug.Print (Val(text1) * 100) - (Int(Val(text2) * 100))
End Sub

Thanks in advance......BluesMatt

[phinds]

in answer to both of your questions regarding the accuracy of numbers, you are apparently working under the mistaken impression that computers work in the decimal number system. They do not. They work in the binary number system which means that while integers can be expressed exactly the same as in decimal, fractions cannot because "rational" numbers are not the same in the two systems. The conversion from decimal to/from binary for fractional numbers cannot be exact, thus producting the differences you see when you convert a number to binary and then back to decimal.

[phinds]

The conversion from decimal to/from binary for fractional numbers cannot be exact

what I mean to say here was that such conversion MAY not be exact. For numbers that are rational in both systems (1/8 for eample) the conversion IS exact and you get no "rounding" error

[KayJay]

There have been many posts and threads and lots of blood split on this topic

do a search here. U'll find the explnations. I believe member Phinds is quite well versed on this subject. the upshot of the whole thing is that, as in both Ur questions, U were using double, and as certain floating point numbers can never be exactly stored in binary a rounding off occurs. hence the results u see.

HTH

[KayJay]

There. Did I not say Phinds was the person?

[phinds]

There. Did I not say Phinds was the person?

Thanks KayJay. After all that hassle the last time this topic came up, it's nice to see that someone remembers that I knew what I was talking about.

[KayJay]

Yeah!. Its well entrenched now. Man, were U furious LOL.

[brucevde]

This relates to your first question as well.

Single and Double datatypes are "approximate numeric data types". They cannot store decimals with complete accuracy.

If you need absolute decimal accuracy use one of the format statements (Format$, FormatNumber etc..) or use the Currency data type. The Currency data type is an "exact numeric data type".

[BluesMatt]

OK. Thanks for the replies. I believe I understand what your are saying. Now, here's the part I didn't say earlier.....I need the user of my program to enter numbers in decimal (the format of the numbers is X.XX). Once a number has been entered (as text in a text box) I need to convert the text to a number so that I can verify that the entered "number" is within an allowable range AND that the entered "number" has no more than 2 "digits" to the right of the decimal point.

My initial apporach was to use VAL to convert the text and do some (what I thought was simple) math to do the tests. Then I ran into this rounding issue and hence the earlier posts.
Then I tried CDbl and had similiar results.

How does one code something like this? It seems like the only choice is to force the user to enter only text that looks like integers (in other words, don't have the user enter a decimal point). (Using only integers will always allow for an EXACT conversion to binary and avoid rounding issues.)

Thanks for your comments........BluesMatt

[KayJay]

Use Round(). Though it has its own problems

Private Sub Command1_Click()
Dim someNumber As Double
someNumber = 12345.6789
someNumber = Round(someNumber, 2)
Debug.Print someNumber
End Sub

[phinds]

testing for "close" as opposed to "exact" will sometimes fix such problems.

For example, if you take the difference between two floating point numbers that you believe should be the same, it is NOT a good idea to test the result for 0.0 but rather to test that the absolute value of the difference is less than some really small number.

[BluesMatt]

Thanks for all the replies.

Brucevde......I changed the program to:

Option Explicit

Dim temp1 As Currency

Private Sub Form_Load()
For temp1 = 4 To 4.19 Step 0.001
Debug.Print temp1
Next temp1
End Sub

and produced results more to my liking. I will continue to test using the currency datatype.

[Shaggy Hiker]

Since you are concerned of the number of digits to the right of the decimal, why not test the string itself rather than the number derived from the string? If there is a decimal (InStr() would tell you that), how many characters follow it? I'm probably off by one or so, but it seems like len(string)-InStr(string, ".") would produce either the length of the string (if no decimal), or a number that must be less than 4.

[BluesMatt]

Thanks Shaggy. Yes, I had the same thought (especially after running into this conversion error).