Find 4 consecutive occurrences

My sheet is used to enter lab test results in a row. I am entering the test date and result (e.g. cells H4:K4 - "3/13 N"; "3/20 P"; "3/27 N"; "4/3 N"...). My goal is to have a Status cell (B4) return a "Yes" when four consecutive "N" results (negative) appear (informing me that the testing for that variable can stop). The Status cell would need to return "No" until such time as four consecutive "N" results are entered. Macro is an option, formula would be preferred. Excel 2003. Any ideas? Thanks for your time... Bruce

Assuming you have contiguous data set then perhaps:

=IF(COUNTIF(INDEX($H4:$Z4,MATCH(REPT("Z",255),$H4:$Z4)-MIN(3,COUNTA($H4:$Z4))):$Z4,"*N")=4,"Yes","No")

modify range to suit of course...

See this link: Count At Least 4 Consecutive Values

It's not clear from your post, but if you are entering the date and result (i.e. "3/13 N") into the same cell it will may be more difficult to determine if you have four consecutive results if the data portion is changing. If all tests are concluded on the same date then it should not be an issue.

One possible approach might be to use a simple COUNTIF formula in a helper cell and use a MATCH formula to change status to "Yes" when the count of consecutive occurences equals 4.

=COUNTIF(B3:E3,"N")=4
=IF(ISERROR(MATCH(TRUE,E4:I4,0)),"No","Yes")

See attached- which assumes separate entries for dates and results (recommended).

Here is the sheet I am working on. The formulas given don't seem to return the desired result.

You will see the format for lab results entry ("3/27 P") in one cell. I am open to a better way to enter this data to achieve the desired result.

Thanks for the quick response.

i cant see 3/27 P anywhere on that sheet! but assuming its just those y/n then donkyotes function still holds true
=IF(COUNTIF(INDEX($B3:$Z3,MATCH(REPT("Z",255),$B3:$Z3)-MIN(3,COUNTA($B3:$Z3))):$Z3,"*N")=4,"Yes","No")

Cell references adjusted per the sample workbook - it returns the correct value.
=IF(COUNTIF(INDEX($G6:$S6,MATCH(REPT("Z",255),$G6:$S6)-MIN(3,COUNTA($G6:$S6))):$S4,"*N")=4,"Yes","No")

My suggested solution is not valid given the structure of the sample workbook, but was provided before the workbook was made available.

OK, I've update the sheet to show the full scenario. Notice in B6 and B7 the formula returns "No", when, in fact, the four consecutive "N"egative results are in K6:N6 and J7:M7. This should return "Yes" in B6 and B7 , meaning the re-testing can be terminated. Thanks again for your time.

Notice in B6 and B7 the formula returns "No", when, in fact, the four consecutive "N"egative results are in K6:N6 and J7:M7.

As I showed and as DO mentioned: adjust the cell references.

It must be $G$6:$S$6 ( not $Z$6 )

Excellent. Not sure why column 'S' was the sticking point, but it works now.

Thanks to all for your input and time.