I have a column of random numbers in A1:A10. In B1:B10, in each row, I do a True/False Test to see if a number is Less Then or Equal to its equivalent (i.e. B1 = IF(3 is <=A1, TRUE, FALSE). So I have a bunch of random TRUE and FALSE items in Column B.

Now in column C I want to be able to test whether my TRUE results in Column B occur consecutively over a preset interval (range of cells). For example assume I have an interval or range input set to 4. So for example in C4 I want to be able to test whether the value in B4 is TRUE AND whether it is within a 4 cell range (interval) of values that are also TRUE. Effectively I want to identify if my TRUE is part of a collection of TRUEs.

So if you see below, I want to isolate the TRUE values in column B that are part of a 4 cell range (interval) AND are TRUE. In this example it would be the range B3:B6 that is the relevant range that conforms to the interval I have set up. It is a forward looking range so B7 would be excluded from that range. So C3:C6 would be TRUE and C7 would be FALSE. I am assuming the formula in C needs to incorporate an OFFSET function incorporating the interval and/or it needs to be an Array function.

Number Test = 3

Interval = 4

A1 7; B1 TRUE; C1 FALSE

A2 1; B2 FALSE; C2 FALSE

A3 10; B3 TRUE; C3 TRUE

A4 12; B4 TRUE; C4 TRUE

A5 15; B5 TRUE; C5 TRUE

A6 16; B6 TRUE; C6 TRUE

A7 2; B7 TRUE; C7 FALSE

A8 1; B8 FALSE; C8 FALSE

A9 0; B9 TRUE; C9 FALSE

A10 1; B10 FALSE; C10 FALSE

Any ideas?

Thanks in advance