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 number


sheela wrote:

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.

Subjectname year month day1 day2€¦€¦€¦€¦..day30 day31

Name 1990 January 2 0 4 4
Name 1990 February 3 1 null null
Name 1990 March 2 null 3 1
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 couldnt use match function here.

3) If there are any 4s in a month, how many consecutive 4s are there?

Thank you in advance for any help,
sheela.

--

Dave Peterson