software

I have some data recorded for every day for some subjects.
And the possible recorded value on each day is: 0 or 1 or 2 or 3 or 4 or null.
I have this type of data for number of subjects over many months.
My data looks like below.

Subjectnameyearmonth day1day2…………..day30day31

Name1990January 2044
Name1990February 31nullnull
Name1990March 2null31
So on.

I need to calculate the following 3 for each row.
1) On which day of the month first occurrence of the 4 comes?

This one I figured out using match() function which I paste below.
=MATCH(4,N2:AR2,0).

2) On which day of the month last occurrence of the 4 comes?
My data can not be sorted, and I couldn’t use match function here.

3) If there are any 4’s in a month, how many consecutive 4’s are there?Thank you in advance for any help,
sheela.

How about just a reply for #2.

This will return the column number that has the last 4:

=LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))

so
=LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))-column(N2) 1
will return the day numbersheela wrote:
gt;
gt; I have some data recorded for every day for some subjects.
gt; And the possible recorded value on each day is: 0 or 1 or 2 or 3 or 4 or null.
gt; I have this type of data for number of subjects over many months.
gt; My data looks like below.
gt;
gt; Subjectname year month day1 day2…………..day30 day31
gt;
gt; Name 1990 January 2 0 4 4
gt; Name 1990 February 3 1 null null
gt; Name 1990 March 2 null 3 1
gt; So on.
gt;
gt; I need to calculate the following 3 for each row.
gt; 1) On which day of the month first occurrence of the 4 comes?
gt;
gt; This one I figured out using match() function which I paste below.
gt; =MATCH(4,N2:AR2,0).
gt;
gt; 2) On which day of the month last occurrence of the 4 comes?
gt; My data can not be sorted, and I couldn’t use match function here.
gt;
gt; 3) If there are any 4’s in a month, how many consecutive 4’s are there?
gt;
gt; Thank you in advance for any help,
gt; sheela.

--

Dave Peterson

Try this in AS2:

=INDEX($N$1:$AR$1,MATCH(4,$N2:$AR2,0))

And this in AT2:

=LOOKUP(2,1/($N2:$AR2=4),$N$1:$AR$1)

And copy down as needed.

2 out of 3 !
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================quot;sheelaquot; gt; wrote in message
...
gt; I have some data recorded for every day for some subjects.
gt; And the possible recorded value on each day is: 0 or 1 or 2 or 3 or 4 or
null.
gt; I have this type of data for number of subjects over many months.
gt; My data looks like below.
gt;
gt; Subjectname year month day1 day2…………..day30 day31
gt;
gt; Name 1990 January 2 0 4 4
gt; Name 1990 February 3 1 null null
gt; Name 1990 March 2 null 3 1
gt; So on.
gt;
gt; I need to calculate the following 3 for each row.
gt; 1) On which day of the month first occurrence of the 4 comes?
gt;
gt; This one I figured out using match() function which I paste below.
gt; =MATCH(4,N2:AR2,0).
gt;
gt; 2) On which day of the month last occurrence of the 4 comes?
gt; My data can not be sorted, and I couldn’t use match function here.
gt;
gt; 3) If there are any 4’s in a month, how many consecutive 4’s are there?
gt;
gt;
gt; Thank you in advance for any help,
gt; sheela.Hello Dave Peterson:

The given formula worked just perfect. But I couldn’t understand how this
function is working.
Why the look up value is is 2? And the what does look up vector 1/(N2:AR2=4)
indicate?
And finally how is it working on unsorted data.
I am sorry I thought I could understand, but seems it is tricky. Thank you
very much for your help.

--
Thank you in advance for any help,
sheela.quot;Dave Petersonquot; wrote:

gt; How about just a reply for #2.
gt;
gt; This will return the column number that has the last 4:
gt;
gt; =LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))
gt;
gt; so
gt; =LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))-column(N2) 1
gt; will return the day number
gt;
gt;
gt; sheela wrote:
gt; gt;
gt; gt; I have some data recorded for every day for some subjects.
gt; gt; And the possible recorded value on each day is: 0 or 1 or 2 or 3 or 4 or null.
gt; gt; I have this type of data for number of subjects over many months.
gt; gt; My data looks like below.
gt; gt;
gt; gt; Subjectname year month day1 day2…………..day30 day31
gt; gt;
gt; gt; Name 1990 January 2 0 4 4
gt; gt; Name 1990 February 3 1 null null
gt; gt; Name 1990 March 2 null 3 1
gt; gt; So on.
gt; gt;
gt; gt; I need to calculate the following 3 for each row.
gt; gt; 1) On which day of the month first occurrence of the 4 comes?
gt; gt;
gt; gt; This one I figured out using match() function which I paste below.
gt; gt; =MATCH(4,N2:AR2,0).
gt; gt;
gt; gt; 2) On which day of the month last occurrence of the 4 comes?
gt; gt; My data can not be sorted, and I couldn’t use match function here.
gt; gt;
gt; gt; 3) If there are any 4’s in a month, how many consecutive 4’s are there?
gt; gt;
gt; gt; Thank you in advance for any help,
gt; gt; sheela.
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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?

--
Thank you in advance for any help,
sheela.quot;sheelaquot; wrote:

gt; I have some data recorded for every day for some subjects.
gt; And the possible recorded value on each day is: 0 or 1 or 2 or 3 or 4 or null.
gt; I have this type of data for number of subjects over many months.
gt; My data looks like below.
gt;
gt; Subjectnameyearmonth day1day2…………..day30day31
gt;
gt; Name1990January 2044
gt; Name1990February 31nullnull
gt; Name1990March 2null31
gt; So on.
gt;
gt; I need to calculate the following 3 for each row.
gt; 1) On which day of the month first occurrence of the 4 comes?
gt;
gt; This one I figured out using match() function which I paste below.
gt; =MATCH(4,N2:AR2,0).
gt;
gt; 2) On which day of the month last occurrence of the 4 comes?
gt; My data can not be sorted, and I couldn’t use match function here.
gt;
gt; 3) If there are any 4’s in a month, how many consecutive 4’s are there?
gt;
gt;
gt; Thank you in advance for any help,
gt; sheela.

1/(n2:a42=4)
returns an array of
1's and errors.

lookup(2, that array) will search for the first 2 in that array. Since there is
no 2, it'll find that last 1 and use that. And the last 1 in that array
corresponds to the last cell in n2:Ar2 that equals 4.

Good luck on #3.

sheela wrote:
gt;
gt; Hello Dave Peterson:
gt;
gt; The given formula worked just perfect. But I couldn’t understand how this
gt; function is working.
gt; Why the look up value is is 2? And the what does look up vector 1/(N2:AR2=4)
gt; indicate?
gt; And finally how is it working on unsorted data.
gt; I am sorry I thought I could understand, but seems it is tricky. Thank you
gt; very much for your help.
gt;
gt; --
gt; Thank you in advance for any help,
gt; sheela.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; How about just a reply for #2.
gt; gt;
gt; gt; This will return the column number that has the last 4:
gt; gt;
gt; gt; =LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))
gt; gt;
gt; gt; so
gt; gt; =LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))-column(N2) 1
gt; gt; will return the day number
gt; gt;
gt; gt;
gt; gt; sheela wrote:
gt; gt; gt;
gt; gt; gt; I have some data recorded for every day for some subjects.
gt; gt; gt; And the possible recorded value on each day is: 0 or 1 or 2 or 3 or 4 or null.
gt; gt; gt; I have this type of data for number of subjects over many months.
gt; gt; gt; My data looks like below.
gt; gt; gt;
gt; gt; gt; Subjectname year month day1 day2…………..day30 day31
gt; gt; gt;
gt; gt; gt; Name 1990 January 2 0 4 4
gt; gt; gt; Name 1990 February 3 1 null null
gt; gt; gt; Name 1990 March 2 null 3 1
gt; gt; gt; So on.
gt; gt; gt;
gt; gt; gt; I need to calculate the following 3 for each row.
gt; gt; gt; 1) On which day of the month first occurrence of the 4 comes?
gt; gt; gt;
gt; gt; gt; This one I figured out using match() function which I paste below.
gt; gt; gt; =MATCH(4,N2:AR2,0).
gt; gt; gt;
gt; gt; gt; 2) On which day of the month last occurrence of the 4 comes?
gt; gt; gt; My data can not be sorted, and I couldn’t use match function here.
gt; gt; gt;
gt; gt; gt; 3) If there are any 4’s in a month, how many consecutive 4’s are there?
gt; gt; gt;
gt; gt; gt; Thank you in advance for any help,
gt; gt; gt; sheela.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

1/(n2:a42=4)
should have been
1/(n2:aR2=4)

Dave Peterson wrote:
gt;
gt; 1/(n2:a42=4)
gt; returns an array of
gt; 1's and errors.
gt;
gt; lookup(2, that array) will search for the first 2 in that array. Since there is
gt; no 2, it'll find that last 1 and use that. And the last 1 in that array
gt; corresponds to the last cell in n2:Ar2 that equals 4.
gt;
gt; Good luck on #3.
gt;
gt; sheela wrote:
gt; gt;
gt; gt; Hello Dave Peterson:
gt; gt;
gt; gt; The given formula worked just perfect. But I couldn’t understand how this
gt; gt; function is working.
gt; gt; Why the look up value is is 2? And the what does look up vector 1/(N2:AR2=4)
gt; gt; indicate?
gt; gt; And finally how is it working on unsorted data.
gt; gt; I am sorry I thought I could understand, but seems it is tricky. Thank you
gt; gt; very much for your help.
gt; gt;
gt; gt; --
gt; gt; Thank you in advance for any help,
gt; gt; sheela.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; How about just a reply for #2.
gt; gt; gt;
gt; gt; gt; This will return the column number that has the last 4:
gt; gt; gt;
gt; gt; gt; =LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))
gt; gt; gt;
gt; gt; gt; so
gt; gt; gt; =LOOKUP(2,1/(N2:AR2=4),COLUMN(N2:AR2))-column(N2) 1
gt; gt; gt; will return the day number
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; sheela wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I have some data recorded for every day for some subjects.
gt; gt; gt; gt; And the possible recorded value on each day is: 0 or 1 or 2 or 3 or 4 or null.
gt; gt; gt; gt; I have this type of data for number of subjects over many months.
gt; gt; gt; gt; My data looks like below.
gt; gt; gt; gt;
gt; gt; gt; gt; Subjectname year month day1 day2…………..day30 day31
gt; gt; gt; gt;
gt; gt; gt; gt; Name 1990 January 2 0 4 4
gt; gt; gt; gt; Name 1990 February 3 1 null null
gt; gt; gt; gt; Name 1990 March 2 null 3 1
gt; gt; gt; gt; So on.
gt; gt; gt; gt;
gt; gt; gt; gt; I need to calculate the following 3 for each row.
gt; gt; gt; gt; 1) On which day of the month first occurrence of the 4 comes?
gt; gt; gt; gt;
gt; gt; gt; gt; This one I figured out using match() function which I paste below.
gt; gt; gt; gt; =MATCH(4,N2:AR2,0).
gt; gt; gt; gt;
gt; gt; gt; gt; 2) On which day of the month last occurrence of the 4 comes?
gt; gt; gt; gt; My data can not be sorted, and I couldn’t use match function here.
gt; gt; gt; gt;
gt; gt; gt; gt; 3) If there are any 4’s in a month, how many consecutive 4’s are there?
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you in advance for any help,
gt; gt; gt; gt; sheela.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson

--

Dave Peterson

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

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

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

lt;alt-F11gt; 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


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”.
2) If there are less than 10 numbers of “non 4” in between 4 s, we count
them also as consecutive 4.
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.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.
quot;Ron Rosenfeldquot; wrote:

gt; On Tue, 10 Jan 2006 07:51:02 -0800, sheela gt;
gt; wrote:
gt;
gt; gt;I am sorry; I am still not able to figure out the 3rd question my post.
gt; gt;Could some one please give a hint on this one please?
gt;
gt; Well, you could always use a User Defined Function (UDF):
gt;
gt; lt;alt-F11gt; opens the VB Editor.
gt;
gt; Ensure your project is highlighted in the project explorer window, then:
gt;
gt; Insert/Module
gt;
gt; and paste the code below into the window that opens.
gt;
gt; To use this function, enter something like:
gt;
gt; =Consec(range, number)
gt;
gt; where range is the range to be searched, and number the number you wish to test
gt; for maximum consecutives.
gt;
gt; e.g.
gt;
gt; =Consec(N2:AR2,4)
gt;
gt;
gt; ===============================
gt; Option Explicit
gt;
gt; Function Consec(rg As Range, num As Long) As Long
gt; Dim c As Range
gt; Dim t1 As Long, t2 As Long
gt;
gt; For Each c In rg
gt; If c.Value = num Then
gt; t1 = t1 1
gt; Else
gt; t2 = Application.WorksheetFunction.Max(t2, t1)
gt; t1 = 0
gt; End If
gt; Next c
gt;
gt; Consec = Application.WorksheetFunction.Max(t1, t2)
gt;
gt; End Function
gt; ================================
gt;
gt;
gt; --ron
gt;

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

gt;
gt;Hello Ron Rosenfeld:
gt;
gt; Thank you very much for your help. It worked out very well.
gt;Now I had to modify some rules to count the consecutive 4�s.
gt;1) if there is just only a single 4 ( no other consecutive 4�s ) any where
gt;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 = 0gt;2) If there are less than 10 numbers of �non 4� in between 4 s, we count
gt;them also as consecutive 4.

That's also relatively easy. Just use another counter.gt;3) if these consecutive 4�s are ending in the row ( exists on day 30, day
gt;31), then continue counting onto the next row for the consecutive 4�s.
gt;

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?gt;
gt;If the condition 3 is not possible could you please help me with the first
gt;2. If the implementation of condition 3 also possible that would be really
gt;great.
gt;
gt;I would greatly appreciate any help.
gt;
gt;Part of data:
gt;Day1,Day2,Day3,Day28,Day29,Day30,Day31,consec
gt;1,4,3,2,,0,1,0
gt;1,0,0,2,4,4,4,5
gt;4,4,2,0,3,1,0,0
gt;
gt;The consec() function values in the last column, which was calculated based
gt;on the given 7 columns.
gt;
gt;
gt;sheela.
gt;quot;Ron Rosenfeldquot; wrote:
gt;
gt;gt; On Tue, 10 Jan 2006 07:51:02 -0800, sheela gt;
gt;gt; wrote:
gt;gt;
gt;gt; gt;I am sorry; I am still not able to figure out the 3rd question my post.
gt;gt; gt;Could some one please give a hint on this one please?
gt;gt;
gt;gt; Well, you could always use a User Defined Function (UDF):
gt;gt;
gt;gt; lt;alt-F11gt; opens the VB Editor.
gt;gt;
gt;gt; Ensure your project is highlighted in the project explorer window, then:
gt;gt;
gt;gt; Insert/Module
gt;gt;
gt;gt; and paste the code below into the window that opens.
gt;gt;
gt;gt; To use this function, enter something like:
gt;gt;
gt;gt; =Consec(range, number)
gt;gt;
gt;gt; where range is the range to be searched, and number the number you wish to test
gt;gt; for maximum consecutives.
gt;gt;
gt;gt; e.g.
gt;gt;
gt;gt; =Consec(N2:AR2,4)
gt;gt;
gt;gt;
gt;gt; ===============================
gt;gt; Option Explicit
gt;gt;
gt;gt; Function Consec(rg As Range, num As Long) As Long
gt;gt; Dim c As Range
gt;gt; Dim t1 As Long, t2 As Long
gt;gt;
gt;gt; For Each c In rg
gt;gt; If c.Value = num Then
gt;gt; t1 = t1 1
gt;gt; Else
gt;gt; t2 = Application.WorksheetFunction.Max(t2, t1)
gt;gt; t1 = 0
gt;gt; End If
gt;gt; Next c
gt;gt;
gt;gt; Consec = Application.WorksheetFunction.Max(t1, t2)
gt;gt;
gt;gt; End Function
gt;gt; ================================
gt;gt;
gt;gt;
gt;gt; --ron
gt;gt;

--ron