Finding if a number is less than a setpoint in a column

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. Any help will be appreciated.

If I get you right this could tell you:

4646 1 100 <-- set point
654654 1
2132 1
2313 1
45 1
212 0
545 1
15663 2
21345 2
465 2
213 3
5646 3
213 3
165 3
136 3
46 4
21 3
3654 2
321 2
3 2
4646
654654
2132
2313
45
212
545
15663
21345
465
213
5646
213
165
136
46

Formula in C2: =COUNTIF(A2:OFFSET(A2;9;0);"<"&$D$2)
Copy down as required

Data in A2:A??
Set point in D2
This example counts 10 rows ahead, change to 60.
Read every number in C as "this many hits from here to x rows further
down

Hans

Assuming source numbers in A1:A20000
With C1 containing the set point: 250
Try in say, B1:
=SUMPRODUCT((OFFSET(A1,,,60)<$C$1)*(OFFSET(A1,,,60)>0))
Copy down B1 to B19940
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fifi" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
> Any help will be appreciated.
>
>
> --
> Fifi
> ------------------------------------------------------------------------
> Fifi's Profile:
http://www.excelforum.com/member.php...o&userid=12325
> View this thread: http://www.excelforum.com/showthread...hreadid=511219
>

Hi!

Here's one way:

Requires 2 helper columns.

Assume your data is in the range A1:A20000.

Insert a new row 1.

In B1 enter 1.

Enter this formula in B2 and copy down to B20001:

=IF(A2<75,"",1)

In B20002 enter 1.

Enter this formula in C2 and copy down to C20002:

=IF(B2<>"",COUNTBLANK(INDEX($B$1:B1,MATCH(2,$B$1:B1)):B2),"")

Now, to count the number of times there are 60 consecutive rows with values
<75:

=COUNTIF(C:C,60)

Biff

"Fifi" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
> Any help will be appreciated.
>
>
> --
> Fifi
> ------------------------------------------------------------------------
> Fifi's Profile:
> http://www.excelforum.com/member.php...o&userid=12325
> View this thread: http://www.excelforum.com/showthread...hreadid=511219
>

Note:

I did my tests on values <75. Just change the the reference to 75 in the
formula with whatever your set point value might be.

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> Here's one way:
>
> Requires 2 helper columns.
>
> Assume your data is in the range A1:A20000.
>
> Insert a new row 1.
>
> In B1 enter 1.
>
> Enter this formula in B2 and copy down to B20001:
>
> =IF(A2<75,"",1)
>
> In B20002 enter 1.
>
> Enter this formula in C2 and copy down to C20002:
>
> =IF(B2<>"",COUNTBLANK(INDEX($B$1:B1,MATCH(2,$B$1:B1)):B2),"")
>
> Now, to count the number of times there are 60 consecutive rows with
> values <75:
>
> =COUNTIF(C:C,60)
>
> Biff
>
> "Fifi" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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.
>> Any help will be appreciated.
>>
>>
>> --
>> Fifi
>> ------------------------------------------------------------------------
>> Fifi's Profile:
>> http://www.excelforum.com/member.php...o&userid=12325
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=511219
>>
>
>

Thanks to everybody. I will try the 3 methods and alet yu know how it goes. Thanks again.

Max, does the formula =SUMPRODUCT((OFFSET(A1,,,60)<$C$1)*(OFFSET(A1,,,60 )>0)) always return 0 if it did not find any number within the 60 consecutive numbers below the set point? I have been using it but it always return 0. Thanks for the help.

Max, just another question: For a test I put 200 in col b2 to b68 and the setpoint wat set at 315. It returned in col d2 to d69:
59
60
60
60
60
60
60
60
60
59
58
57
56
55
54
53
52
51
50
49
48
47
46
45
44
43
42
41
40
39
38
37
36
35
34
33
32
31
30
29
28
27
26
25
24
23
22
21
20
19
18
17
16
15
14
13
12
11
10
9
8
7
6
5
4
3
2
1

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? Thanks again

"Fifi" wrote:
> ... does the formula
> =SUMPRODUCT((OFFSET(A1,,,60)<$C$1)*(OFFSET(A1,,,60
> )>0)) always return 0 if it did not find any number within the 60
> consecutive numbers below the set point?
> I have been using it but it always return 0.

You probably have text numbers somewhere which is throwing the calcs off ..

If there's the possibility of text numbers occuring within either the source
col and/or in the set-point, we could try this slightly revised:
=SUMPRODUCT((OFFSET(A1,,,60)+0<$C$1+0)*(OFFSET(A1,,,60)+0>0)
The "+0" will coerce text numbers, if any, to real numbers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---