The formula you posted had some errors, i used this:
=IF(AND(P23>P22;SUM(O19:O22)<4);1;0) (for 4 consecutive rows)
To have in a cell the number of consecutive rows with 1 value, You must use sum with offset to accomplish that.
Assuming that in cell M3 you enter the number of consecutive rows with 1 value:


=IF(AND(P23>P22;SUM(OFFSET(O23;-$M$3;0;$M$3;1))<M$3);1;0) . Copy this down. (you must replace formula separators ; to ,)
In this case, OFFSET function creates a range of 1 column, with the number of rows = M3, placed above O23(that is the number of rows above O23: -$M$3)

Thank you, offset was what I needed. I'd worked out I would have to use something like sum. It would also need a check that the top value = 0, otherwise sequences like:

1
0
1
1

would be picked up as "3 in a row" and it would set to 0, so a simple AND(SUM(the n above offset)=n-1,n above = 0) works well

Cheers! I'm sure I'll probably be using Offset a lot more, you sure do learn a lot about excel coming across these tricky problems.

Great! Then you can mark your thread as solved (under thread tools, select-mark this thread as solved)