Need help to identify cell contents

Please help! Been trying for days to work this out. I have a spreadsheet in Excel97 that collates responses to questions in a Y or N format. I want to be told when a question has been answered N for the previous 4 weeks or more. The week numbers are column headings and the questions are listed down the rows.

If it can do this, what I then want it to do, its disregard the 4 "N" entries when a Y is eventually posted.

Hope you can help or at least tell me if I am wasting my time trying to work out something that is impossible!

Thanks very much.

[Andy Gee]

Let me get this straight...
When you say disregard the last 4 N's do you want it to total the numbers of responses and ignore any 4 N's in a row?

Sorry, didn't make myself very clear! I want it to tell me when an N has been recorded in 4 consectutive weeks, say 1-4 Then...if a Y is recorded in week 5, but N's in weeks 6-10 I want it to tell me about the 4 N's in 6-10 but not the ones in 1-4. (As these have already been notified to me)

Is that any clearer? Hope you can help!

[Juan Pablo González]

Not really. Can you put some example data ? and what you expect to get as a result ?

Week No 1 2 3 4 5 6 7
Q1 y n n n n y n
Q2 n n n n y y y

From the information in the table I want it to identify that Q1 was "n" for 4 weeks in a row at week 5 and Q2 was also "n" for 4 weeks in a row at week 4. I don't want it to identify "n" for less than 4 weeks. Would like it to say something along the lines of "At week 5 Q1 has been negative for 4 weeks", but this has to appear on another worksheet as an overview of approx 50 questions.