:
(orig. post)
.. I have a col of numbers and need to find if the number in any row
(dis-regarding 0) is less than a set point say 250 for for the next 60
consecutive rows. So, if I have col of 20,000 numbers how do I find
that there are no 60 consecutive rows whose values are lesser than 250.
(feedback ..)
.. I am assuming that this formula returns the row number of the values
below the setpoint?? Also, why is the return value row number lower in
row row 68? Does the formula work in descending order?
Let's take the last revised, adapted formula:
In D2:
=SUMPRODUCT((OFFSET(B2,,,60)+0<$C$1+0)*(OFFSET(B2,,,60)+0>0))
In D2, the formula will return the count of 60 consecutive cells from the
startpoint, i.e. the anchor cell "B2" in the OFFSET. The "60" fig is the
height param in the OFFSET. In D2, the range is hence B2:B61, and the
formula checks for the 2 conditions to be satisfied (within B2:B61) which
you spelt out in your orig. post, i.e. quote:
a. "values are lesser than 250"
b. ".. (dis-regarding 0) .."
So if B2:B61 contains 60 numbers between zero and 250 (the 2 conditions),
D2 will return: 60
When D2 is copied down to D3, the anchor cell "B2" will change relatively to
B3, and the range returned by the OFFSET is now shifted down to: B3:B62,
i.e. the next set of 60 consec cells starting from B3.
(The set-point cell, C1 is assumed fixed throughout)
Likewise, if B3:B62 contains 60 numbers between zero and 250 (the 2
conditions), D3 will also return: 60
And so on, down the line
If the count returned in col D, say, in D10 is less than 60, that means
that the range B10:B69 contains less than 60 cells which satisfy the 2
conditions. Empty cells, if any, will be evaluated as zero.
And if desired, we could put the suggested formula within an IF construct to
flag the start-points of any 60 consec cells range within the source col B
where the count returned is below 60, i.e. use something like this instead
in D2:
=IF(SUMPRODUCT((OFFSET(B2,,,60)+0<$C$1+0)*(OFFSET(B2,,,60)+0>0))<60,"X","")
Hope the above clarifies it a little better ..
---