minutes aren't minutes?

[imda14u]

Hi all,


I have long list of measurement timings with an interval of 10 minutes.

Like this:

time measure
1-1-2008 0:00 120
1-1-2008 0:10 117
1-1-2008 0:20 118

and so on for a whole month.

Sometimes however, there's a gap in time greater than 10 minutes.
I need to fill that gap with the missing time and also add NA for the
measure column. So I wrote the following rather straightforward code:

Sub fillmissing()
' create a gap of 10 minutes
difference = 1 / 24 / 6

Range("a2").Select
Do Until ActiveCell.Text = ""
jump = ActiveCell.Offset(1, 0).Value - ActiveCell.Value
If jump = dfference Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"

End If
Loop
End Sub

But, somehow Excel finds 10 minutes unequal to 1/24/6
So, when i run the code i end up with a long list of new gaps of 10
minutes and a NA value in the second column.

I can't put my finger on it.

Does anybody have any suggestions?

Thanks in advance,


Sybolt

 

[Bernard Liengme]

Hi Sybolt,
You have run into IEEE round-off error - see below
Replace the branch part of your code by:

If Abs(jump - difference) > 0.000000000001 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"
Else
ActiveCell.Offset(1, 0).Select
End If


Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1
best wishes

 

[Niek Otten]

Hi Sybolt

Always use Option Explicit

Now you allowed yourself a typo without VBE detecting it

If you correct it, you'll find another error, but that's a different story

 

[imda14u]

Bernard Liengme schreef:

Replace the branch part of your code by:

If Abs(jump - difference) > 0.000000000001 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"
Else
ActiveCell.Offset(1, 0).Select
End If

Thanks for the quick reply,

I tried your code but it runs only 1 time correctly.
for the next rows the code just adds the extra row and the NA

just as my code did.

Maybe I haven't put the code correctly it now looks
like this:

Sub fillmissing()
difference = 1 / 24 / 6

Range("a2").Select
Do Until ActiveCell.Text = ""
jump = ActiveCell.Offset(1, 0).Value - ActiveCell.Value
If Abs(jump - difference) > 0.000000000001 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub



Any suggestions?

Sybolt

 

[imda14u]

Niek Otten schreef:

Hi Sybolt

Always use Option Explicit

Now you allowed yourself a typo without VBE detecting it

If you correct it, you'll find another error, but that's a different story

Niek,

thanks fot the reply,

I know about the option explicit statement, but sometimes it doesn't
seem worth the trouble.

Tell me about the different story.




Sybolt

 

[Peter T]

Niek cited your own routine as a perfect example of why you should always
use Option Explicit

In case you still haven't seen it, you have

"difference" and "dfference"

(I haven't looked to see if there are other errors too)

Regards,
Peter T

 

[Niek Otten]

You should'nt have told him what the typo was, Peter :-}

<sometimes it doesn't seem worth the trouble>

OK. You had a problem. You probably (hopefully) spent some effort trying to
solve it.
You took the trouble to post a message. Several people (no idea how many)
tried to reconstruct your problem.
Some answered.

Now what do you think about <sometimes it doesn't seem worth the trouble>

It's ALWAYS worth the trouble. BTW, What trouble?

I think there's no excuse for not using Option Explicit.

 

[Niek Otten]

<BTW, What trouble?>

Once only:

Tools>Options>Click "Require Variable declaration".

It will insert the Option Explicit line autoamtically