On Tue, 10 Jan 2006 15:31:02 -0800, sheela
wrote:


Hello Ron Rosenfeld:

Thank you very much for your help. It worked out very well.
Now I had to modify some rules to count the consecutive 4’s.
1) if there is just only a single 4 ( no other consecutive 4’s ) any where
in the row we ignore that 4 and treat it as “ non 4”.

That's easy. Just add something like: if t2 = 1 then t2 = 0


2) If there are less than 10 numbers of “non 4” in between 4 s, we count
them also as consecutive 4.

That's also relatively easy. Just use another counter.


3) if these consecutive 4’s are ending in the row ( exists on day 30, day
31), then continue counting onto the next row for the consecutive 4’s.


Does this interact with rule 2? Do the last 2 days have to be a 4 in order to
go on to the next row? Or if, for example, Day 25 is a 4 and Day 2 is a 4, and
the initial row is a 31 day month, do we count 9 consecutive 4's?



If the condition 3 is not possible could you please help me with the first
2. If the implementation of condition 3 also possible that would be really
great.

I would greatly appreciate any help.

Part of data:
Day1,Day2,Day3,Day28,Day29,Day30,Day31,consec
1,4,3,2,,0,1,0
1,0,0,2,4,4,4,5
4,4,2,0,3,1,0,0

The consec() function values in the last column, which was calculated based
on the given 7 columns.


sheela.
"Ron Rosenfeld" wrote:

On Tue, 10 Jan 2006 07:51:02 -0800, sheela
wrote:

I am sorry; I am still not able to figure out the 3rd question my post.
Could some one please give a hint on this one please?

Well, you could always use a User Defined Function (UDF):

<alt-F11 opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then:

Insert/Module

and paste the code below into the window that opens.

To use this function, enter something like:

=Consec(range, number)

where range is the range to be searched, and number the number you wish to test
for maximum consecutives.

e.g.

=Consec(N2:AR2,4)


===============================
Option Explicit

Function Consec(rg As Range, num As Long) As Long
Dim c As Range
Dim t1 As Long, t2 As Long

For Each c In rg
If c.Value = num Then
t1 = t1 + 1
Else
t2 = Application.WorksheetFunction.Max(t2, t1)
t1 = 0
End If
Next c

Consec = Application.WorksheetFunction.Max(t1, t2)

End Function
================================


--ron


--ron