Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
Running this code
Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i Next i End Sub gives this output 1.5 1.6 1.7 1.8 1.9 . Why does the output not include 2.0? and what do I have to do to make the output include 2.0? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
2.1
-- Don Guillett Microsoft MVP Excel SalesAid Software "choi4u" wrote in message ... Running this code Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i Next i End Sub gives this output 1.5 1.6 1.7 1.8 1.9 . Why does the output not include 2.0? and what do I have to do to make the output include 2.0? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
Sub test()
For i = 1.5 To 2.01 Step 0.1 Debug.Print i Next i End Sub -- Gary''s Student - gsnu2007h |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
To add to thos ecomments try this
Sub test() Dim i As Single For i = 1.5 To 2 Step 0.1 p = i + 0.1 2 Debug.Print i Debug.Print p Next i End Sub As you will note as soon as i = 1.9 then i + 0.1 is greater than 2 (True) so it never executes. Mike "choi4u" wrote: Running this code Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i Next i End Sub gives this output 1.5 1.6 1.7 1.8 1.9 . Why does the output not include 2.0? and what do I have to do to make the output include 2.0? Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
I'd guess that there was an internal rounding problem that you got hit with.
I'd use: Sub test() Dim i As Long For i = 15 To 20 Step 1 Debug.Print i / 10 Next i End Sub choi4u wrote: Running this code Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i Next i End Sub gives this output 1.5 1.6 1.7 1.8 1.9 . Why does the output not include 2.0? and what do I have to do to make the output include 2.0? Thanks in advance. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
The problem has to do with floating point representation and the inability
of VB (actually, any language) of being able to store all fractions in a given integer data type (the problem is akin to the decimal representation of 2/3, a decimal point followed by an unending string of 6's, invariably the number is written as a fixed number of 6's followed by an ending 7)... whatever has accumulated in the loop variable "i" at the 1.9 stage, adding 0.1 to it gives a number ever so slightly greater than 2 which is outside of the ending loop limit, hence the loop ends at the 1.9 value. The way around this problem is to not use floating point numbers. For example... Sub test() For i = 15 To 20 Debug.Print i / 10 Next i End Sub (note the modified loop limits) where whenever you would have used "i" in your original loop code, you would use "i/10" instead. Rick "choi4u" wrote in message ... Running this code Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i Next i End Sub gives this output 1.5 1.6 1.7 1.8 1.9 . Why does the output not include 2.0? and what do I have to do to make the output include 2.0? Thanks in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
Here are some links to WebPages that will show you the problem in more
detail... INFO: Visual Basic and Arithmetic Precision http://support.microsoft.com/default...NoWebContent=1 (Complete) Tutorial to Understand IEEE Floating-Point Errors http://support.microsoft.com/default...NoWebContent=1 What Every Computer Scientist Should Know About Floating Point http://docs.sun.com/source/806-3568/ncg_goldberg.html Rick "Rick Rothstein (MVP - VB)" wrote in message ... The problem has to do with floating point representation and the inability of VB (actually, any language) of being able to store all fractions in a given integer data type (the problem is akin to the decimal representation of 2/3, a decimal point followed by an unending string of 6's, invariably the number is written as a fixed number of 6's followed by an ending 7)... whatever has accumulated in the loop variable "i" at the 1.9 stage, adding 0.1 to it gives a number ever so slightly greater than 2 which is outside of the ending loop limit, hence the loop ends at the 1.9 value. The way around this problem is to not use floating point numbers. For example... Sub test() For i = 15 To 20 Debug.Print i / 10 Next i End Sub (note the modified loop limits) where whenever you would have used "i" in your original loop code, you would use "i/10" instead. Rick "choi4u" wrote in message ... Running this code Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i Next i End Sub gives this output 1.5 1.6 1.7 1.8 1.9 . Why does the output not include 2.0? and what do I have to do to make the output include 2.0? Thanks in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop with step increment of 0.1
On May 16, 9:33 am, choi4u wrote:
Running this code Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i Next i End Sub gives this output 1.5 1.6 1.7 1.8 1.9 . Why does the output not include 2.0? and what do I have to do to make the output include 2.0? Thanks in advance. It's due to the age old binary to decimal conversion problem. Computers use binary representations of real numbers that often (most often) do not convert exactly to their decimal equivalents without some remainder. This leads to the commonly occurring problem that you see here. As Dan Guillett suggested, one workaround is simply increase the end value to compensate for the error, though I might use 2.01 to keep from confusing the issue more - that is, "Why isn't 2.1 in the list?" Another approach might be to convert the loop from a FOR to a Do Until with a Round function to correct for the error ... Sub test() step = 0.1 i = 1.5 Do Until i 2 Debug.Print i i = Round(i + step, 1) Loop End Sub Another way that would work is to round the index in the FOR loop ... Sub test() For i = 1.5 To 2 Step 0.1 Debug.Print i i = Round(i, 1) Next i End Sub Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
non-linear step count loop syntax? | Excel Programming | |||
Do loop until, step down row - copy paste - not what it seems. | Excel Programming | |||
How to increment within For Next Loop | Excel Discussion (Misc queries) | |||
Increment through For Next loop | Excel Programming |