Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing double values
What is the best method for comparing double values. The following loop
produces one too many values. Dim dblStep As Double Dim dblT As Double Dim dlbTMax As Double dblStep = 0.005 dblTMax = 0.060 dblT = 0.0 While dblT < dblTMax MsgBox Str(dblT) dblT = dblT + dblStep Wend Produces: 0.000 0.005 0.010 0.015 0.020 0.025 0.030 0.035 0.040 0.045 0.050 0.055 0.060 <- This one should not have been produced. *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing double values
Dim dblStep As Double
Dim dblT As Double Dim dblTMax As Double ' correct misspelling Dim dblStart As Double Dim i as Long dblStep = 0.005 dblTMax = 0.060 + .000001 dblT = 0.0 dblStart = 0.0 i = 1 While dblT < dblTMax MsgBox Str(dblT) dblT = dblStart + (i * dblStep) i = i + 1 Wend Doubles don't always represent a value exactly. Adding a double can accumulate error. -- Regards, Tom Ogilvy "Edward Ulle" wrote in message ... What is the best method for comparing double values. The following loop produces one too many values. Dim dblStep As Double Dim dblT As Double Dim dlbTMax As Double dblStep = 0.005 dblTMax = 0.060 dblT = 0.0 While dblT < dblTMax MsgBox Str(dblT) dblT = dblT + dblStep Wend Produces: 0.000 0.005 0.010 0.015 0.020 0.025 0.030 0.035 0.040 0.045 0.050 0.055 0.060 <- This one should not have been produced. *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing double values
Tom,
I suspected as much but in your opinion, if I am dealing with small numbers what is a minimum tolerance, say 10 to the minus 10 or is that too small? *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing double values
for doubles, VBA/Excel uses the IEEE standard which is approximately 15
digits of precision, so the error is usually around the last couple of digits. How that translates into your "epsilon" would depend on your numbers. Here is an illustrative example of accumulating the results of imprecision: Sub abcd() Dim v As Double v = 0# For i = 1 To 300 v = v + CDbl(1 / 3) Next Debug.Print Format(v, "0.0000000000000000000") Debug.Print Format(CDbl(1 / 3) * 300, "0.0000000000000000000") End Sub -- Regards, Tom Ogilvy "Edward Ulle" wrote in message ... Tom, I suspected as much but in your opinion, if I am dealing with small numbers what is a minimum tolerance, say 10 to the minus 10 or is that too small? *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to double values in a cell | Excel Worksheet Functions | |||
Help with Comparing values and retrieving values in Excel!!!!!! | Excel Worksheet Functions | |||
Comparing values between columns only when there are values in bot | Excel Worksheet Functions | |||
Comparing values in two columns and displaying missing values in n | Excel Programming | |||
Comparing Values | Excel Programming |