Forecast Project- Number patterns

[Jeremiah32]

I’m working on a forecast project where I’m trying to identify number patterns that involve values of zero or/and one that occur within consecutively within a three month period over an 24 month history. The table I’m working with is a text table that has over 800,000 records.

The function “GetConsecutive” listed below returns a true or false value when it comes across occurrences with zero or one within three month period. I attached a copy of test database. Right now, I’ve only tested the function over a seven month period. The function is working as designed. Columns are monthly values called History 01, History 02,, etc.

The next step, which happens to be the hardest, is counting how many times the function identifies these zero or one that occur consecutively within a three month period but over a seven month period. Would anybody have an idea’s how this can be achieved? I was lucky enough to get true or false to work properly.


Public Function getConsecutive(ParamArray varValues() As Variant) As Boolean
Const intConsecutive = 3
Dim varVal As Variant
Dim intCount As Integer

For Each varVal In varValues
If Nz(varVal, 0) = 0 Or Nz(varVal, 0) = 1 Then
intCount = intCount + 1
Else
intCount = 0
End If
If intCount = 3 Then Exit For
Next varVal
If intCount = 3 Then getConsecutive = True
End Function

 

[MajP]

recheck your links, I can not get anything.

 

[JoeAtWork]

I was lucky enough to get true or false to work properly.

I would say it was PHV who got it to work in your other thread, not luck.

Regarding your link, you can't point it to your own hard drive, you need to upload your file to a web server and provide a link from there.