Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)

You need to specify the Step used in the For loop. If you don't specify a
Step value, VBA uses 1.

Dim I As Double
For I = -0.2 To 0.2 Step 0.1
Debug.Print I
Next I


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Harvey" wrote in message
...
Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)

Harvey,

I'm not sure of what you mean by "eliminate roundoff" and what you expect
the loop to do, but the way you have it, it will go through the loop only
once and that's for the first value of -0.2. The next value will be 0.8
which is 0.2. If you specify a step value, you'll get more iterations:

Dim i As Double
For i = -0.2 To 0.2 Step 0.1
Debug.Print i
Next


"Harvey" wrote:

Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)

Sorry! my mistake I forgot to write that step now if you run it you won't get
0 as you expect!! instead very very small number how can we modify the code
to eliminate this error.
Thanks :)

"Vergel Adriano" wrote:

Harvey,

I'm not sure of what you mean by "eliminate roundoff" and what you expect
the loop to do, but the way you have it, it will go through the loop only
once and that's for the first value of -0.2. The next value will be 0.8
which is 0.2. If you specify a step value, you'll get more iterations:

Dim i As Double
For i = -0.2 To 0.2 Step 0.1
Debug.Print i
Next


"Harvey" wrote:

Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)

Sorry, but I still don't understand what the problem is. What "error" are
you trying to eliminate? Maybe if you give a little more detail on what you
intend to do with the loop or what it is that you're trying to get done.

"Harvey" wrote:

Sorry! my mistake I forgot to write that step now if you run it you won't get
0 as you expect!! instead very very small number how can we modify the code
to eliminate this error.
Thanks :)

"Vergel Adriano" wrote:

Harvey,

I'm not sure of what you mean by "eliminate roundoff" and what you expect
the loop to do, but the way you have it, it will go through the loop only
once and that's for the first value of -0.2. The next value will be 0.8
which is 0.2. If you specify a step value, you'll get more iterations:

Dim i As Double
For i = -0.2 To 0.2 Step 0.1
Debug.Print i
Next


"Harvey" wrote:

Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)

Okay try to run this code in VBA and see the reults in immediate window.
sub test
Dim i as double
for i= - 0.2 to 0.2 step 0.1
debug.print i
next
End sub
you must get "0" in the third step but you get a very small number well
this is a very common and known round up error but I want to know is there
any quick fix or trick to eliminate this error in VBA code?
Thanks:)
"Vergel Adriano" wrote:

Sorry, but I still don't understand what the problem is. What "error" are
you trying to eliminate? Maybe if you give a little more detail on what you
intend to do with the loop or what it is that you're trying to get done.

"Harvey" wrote:

Sorry! my mistake I forgot to write that step now if you run it you won't get
0 as you expect!! instead very very small number how can we modify the code
to eliminate this error.
Thanks :)

"Vergel Adriano" wrote:

Harvey,

I'm not sure of what you mean by "eliminate roundoff" and what you expect
the loop to do, but the way you have it, it will go through the loop only
once and that's for the first value of -0.2. The next value will be 0.8
which is 0.2. If you specify a step value, you'll get more iterations:

Dim i As Double
For i = -0.2 To 0.2 Step 0.1
Debug.Print i
Next


"Harvey" wrote:

Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)

Hmmm...

For me, I get zero in the third step, not a very small number. However
the principle is correct - rounding errors occur when you use floating
point math.

You could force the zero with

Public Sub Test2
Dim i As Long
Dim di as Double
For i = -2 to 2
di = i/10
debug.print i, i/10
Next i
End Sub

Ultimately, though, the only way to prevent all rounding errors is to
use only integer math. Most numbers can't be exactly represented in
binary, just as most numbers can't be exactly represented in decimal.


In article ,
Harvey wrote:

Okay try to run this code in VBA and see the reults in immediate window.
sub test
Dim i as double
for i= - 0.2 to 0.2 step 0.1
debug.print i
next
End sub
you must get "0" in the third step but you get a very small number well
this is a very common and known round up error but I want to know is there
any quick fix or trick to eliminate this error in VBA code?
Thanks:)

Thanks you :)

"JE McGimpsey" wrote:

Hmmm...

For me, I get zero in the third step, not a very small number. However
the principle is correct - rounding errors occur when you use floating
point math.

You could force the zero with

Public Sub Test2
Dim i As Long
Dim di as Double
For i = -2 to 2
di = i/10
debug.print i, i/10
Next i
End Sub

Ultimately, though, the only way to prevent all rounding errors is to
use only integer math. Most numbers can't be exactly represented in
binary, just as most numbers can't be exactly represented in decimal.


In article ,
Harvey wrote:

Okay try to run this code in VBA and see the reults in immediate window.
sub test
Dim i as double
for i= - 0.2 to 0.2 step 0.1
debug.print i
next
End sub
you must get "0" in the third step but you get a very small number well
this is a very common and known round up error but I want to know is there
any quick fix or trick to eliminate this error in VBA code?
Thanks:)

I think you meant
For i = -0.3 To 0.2 Step 0.1
since starting at -0.2 does not illustrate your point.

Excel (like almost all software) does binary math. In binary, most
terminating decimal fractions are non-terminating binary fractions that can
only be approximated (just as 1/3 can only be approximated in decimal).

The binary approximations to 0.1, 0.2, and 0.3 have decimal values of
0.100000000000000005551115123125782702118158340454 1015625
0.200000000000000011102230246251565404236316680908 203125
0.299999999999999988897769753748434595763683319091 796875
You don't directly see these approximations, because (as documented in Help)
Excel displays no more than 15 significant figures. However, if you do the
math, you will see that the value of
2.77555756156289135105907917022705078125E-17
that Excel returns for =(-0.3+0.1+0.1+0.1) is exactly correct, given the
initial approximations.

Since the math is right, and the only surprise is the residue of the initial
approximations, there are several approaches that you can take. As J.E.
McGimpsey suggested, you can either stick to interger calculations or at
least integer increments. Another approach would be to recognize that since
your intent is to add/subtract numbers with no more than 1 decimal place, you
can round the results of calculations to reduce the residue of binary
approximations without violence to the calculation. Thus you could use

Sub test()
Dim i As Double
For i = -0.3 To 0.2 Step 0.1
i = Round(i, 1)
Debug.Print i
Next i
End Sub

Yet another approach would be to add code to recognize i as zero if
Abs(i)<epsilon for a suitably small value of epsilon.

Jerry

"Harvey" wrote:

Okay try to run this code in VBA and see the reults in immediate window.
sub test
Dim i as double
for i= - 0.2 to 0.2 step 0.1
debug.print i
next
End sub
you must get "0" in the third step but you get a very small number well
this is a very common and known round up error but I want to know is there
any quick fix or trick to eliminate this error in VBA code?
Thanks:)
"Vergel Adriano" wrote:

Sorry, but I still don't understand what the problem is. What "error" are
you trying to eliminate? Maybe if you give a little more detail on what you
intend to do with the loop or what it is that you're trying to get done.

"Harvey" wrote:

Sorry! my mistake I forgot to write that step now if you run it you won't get
0 as you expect!! instead very very small number how can we modify the code
to eliminate this error.
Thanks :)

"Vergel Adriano" wrote:

Harvey,

I'm not sure of what you mean by "eliminate roundoff" and what you expect
the loop to do, but the way you have it, it will go through the loop only
once and that's for the first value of -0.2. The next value will be 0.8
which is 0.2. If you specify a step value, you'll get more iterations:

Dim i As Double
For i = -0.2 To 0.2 Step 0.1
Debug.Print i
Next


"Harvey" wrote:

Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)

Thank you very much!

"Jerry W. Lewis" wrote:

I think you meant
For i = -0.3 To 0.2 Step 0.1
since starting at -0.2 does not illustrate your point.

Excel (like almost all software) does binary math. In binary, most
terminating decimal fractions are non-terminating binary fractions that can
only be approximated (just as 1/3 can only be approximated in decimal).

The binary approximations to 0.1, 0.2, and 0.3 have decimal values of
0.100000000000000005551115123125782702118158340454 1015625
0.200000000000000011102230246251565404236316680908 203125
0.299999999999999988897769753748434595763683319091 796875
You don't directly see these approximations, because (as documented in Help)
Excel displays no more than 15 significant figures. However, if you do the
math, you will see that the value of
2.77555756156289135105907917022705078125E-17
that Excel returns for =(-0.3+0.1+0.1+0.1) is exactly correct, given the
initial approximations.

Since the math is right, and the only surprise is the residue of the initial
approximations, there are several approaches that you can take. As J.E.
McGimpsey suggested, you can either stick to interger calculations or at
least integer increments. Another approach would be to recognize that since
your intent is to add/subtract numbers with no more than 1 decimal place, you
can round the results of calculations to reduce the residue of binary
approximations without violence to the calculation. Thus you could use

Sub test()
Dim i As Double
For i = -0.3 To 0.2 Step 0.1
i = Round(i, 1)
Debug.Print i
Next i
End Sub

Yet another approach would be to add code to recognize i as zero if
Abs(i)<epsilon for a suitably small value of epsilon.

Jerry

"Harvey" wrote:

Okay try to run this code in VBA and see the reults in immediate window.
sub test
Dim i as double
for i= - 0.2 to 0.2 step 0.1
debug.print i
next
End sub
you must get "0" in the third step but you get a very small number well
this is a very common and known round up error but I want to know is there
any quick fix or trick to eliminate this error in VBA code?
Thanks:)
"Vergel Adriano" wrote:

Sorry, but I still don't understand what the problem is. What "error" are
you trying to eliminate? Maybe if you give a little more detail on what you
intend to do with the loop or what it is that you're trying to get done.

"Harvey" wrote:

Sorry! my mistake I forgot to write that step now if you run it you won't get
0 as you expect!! instead very very small number how can we modify the code
to eliminate this error.
Thanks :)

"Vergel Adriano" wrote:

Harvey,

I'm not sure of what you mean by "eliminate roundoff" and what you expect
the loop to do, but the way you have it, it will go through the loop only
once and that's for the first value of -0.2. The next value will be 0.8
which is 0.2. If you specify a step value, you'll get more iterations:

Dim i As Double
For i = -0.2 To 0.2 Step 0.1
Debug.Print i
Next


"Harvey" wrote:

Anyone knows how to eliminate roundoff error in a loop?

Dim i as double
for i= - 0.2 to 0.2
debug.print i
next

Thanks:)