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

**Fifi** · 02-10-2006, 05:43 PM

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.

flummi · 02-11-2006, 06:10 AM

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

Max · 02-11-2006, 11:30 AM

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
>

Biff · 02-11-2006, 03:30 PM

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
>

Biff · 02-11-2006, 03:40 PM

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
>>
>
>

**Fifi** · 02-13-2006, 10:06 AM

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

**Fifi** · 02-13-2006, 11:46 AM

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** · 02-13-2006, 12:09 PM

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

Max · 02-13-2006, 06:50 PM

"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
---

Max · 02-13-2006, 07:10 PM

"Fifi" wrote:
> .. 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:

You need to adapt the "A1" in the formula to point to "B2" if the data
starts in B2 (instead of the assumed "A1" as stated in my 1st response)

So, together with the revision to cater for the possibility of text numbers,
try in D2:
=SUMPRODUCT((OFFSET(B2,,,60)+0<$C$1+0)*(OFFSET(B2,,,60)+0>0))
and copy D2 down

Let me know if this returns the results you expect on your data ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max · 02-13-2006, 09:40 PM

"Fifi" wrote:
(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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

**Fifi** · 02-14-2006, 10:47 AM

Max, thanks very much the formula =SUMPRODUCT((OFFSET(B2,,,60)+0<$C$1+0)*(OFFSET(B2, ,,60)+0>0)) works very weel for what I wanted. I will stick with that. Once again thanks for all your help.

Max · 02-14-2006, 02:55 PM

Glad it worked for you !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fifi" <[email protected]> wrote in message
news:[email protected]...
>
> Max, thanks very much the formula
> =SUMPRODUCT((OFFSET(B2,,,60)+0<$C$1+0)*(OFFSET(B2, ,,60)+0>0)) works
> very weel for what I wanted. I will stick with that. Once again thanks
> for all your help.