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

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" wrote in message
...

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:B 1)):B2),"")

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

=COUNTIF(C:C,60)

Biff

"Fifi" wrote in message
...

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" wrote in message
...
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:B 1)):B2),"")

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

=COUNTIF(C:C,60)

Biff

"Fifi" wrote in message
...

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.


--
Fifi
------------------------------------------------------------------------
Fifi's Profile: http://www.excelforum.com/member.php...o&userid=12325
View this thread: http://www.excelforum.com/showthread...hreadid=511219

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.


--
Fifi
------------------------------------------------------------------------
Fifi's Profile: http://www.excelforum.com/member.php...o&userid=12325
View this thread: http://www.excelforum.com/showthread...hreadid=511219

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
------------------------------------------------------------------------
Fifi's Profile: http://www.excelforum.com/member.php...o&userid=12325
View this thread: http://www.excelforum.com/showthread...hreadid=511219

"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)+00)
The "+0" will coerce text numbers, if any, to real numbers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---