count the number of new lows within a range of cells

[jbesr1230]

Hello,
Is there a formula or method to count the number of new lows within a
range of cells? For example, the following are in col A rows 5 to 14.
Within this set a new low has been reached 3 times- (96,95,94). What I
would like is a formula or macro that that would produce the results -
3. There are about 9000 rows of data and I am looking for "count of
new lows within the preceding 10 rows" on a rolling basis.

Thanks.
100
101
96
97
98
97
95
97
94
95

 

[schoujar]

Version 1:
Use this if the 9000 or so rows don't have blanks in them.
In this version you just need to select the first cell and run the
macro. (so in your example case you will be selecting A5)


Code:
--------------------
Dim i, r, c, min, count, range As Integer


r = Selection.Row
c = Selection.Column
range = 10 'the number of rows to look at one time

While Len(Cells(r, c).Text) > 0
i = 0
count = 0
min = Cells(r, c).Value
For i = 0 To (range - 1)
If Cells(r + i, c).Value < min Then
min = Cells(r + i, c).Value
If min < Cells(r + i + 1, c).Value Then
count = count + 1
End If
End If
r = r + 1
Next i
MsgBox count
Wend
--------------------



If they do have blanks then you can use version 2 but you will need to
know how many rows you have in total:
Version 2:

Code:
--------------------
Dim j, i, r, c, end, min, count, range As Integer


r = Selection.Row
c = Selection.Column
range = 10 'the number of rows to look at one time
end = 9000 ' the number of rows you want to look at in total

For j = 1 to end
i = 0
count = 0
min = Cells(r, c).Value
For i = 0 To (range - 1)
If Cells(r + i, c).Value < min Then
min = Cells(r + i, c).Value
If min < Cells(r + i + 1, c).Value Then
count = count + 1
End If
End If
r = r + 1
Next i
MsgBox count
Next j

 

[aresen]

Hello,
Is there a formula or method to count the number of new lows within a
range of cells? For example, the following are in col A rows 5 to 14.
Within this set a new low has been reached 3 times- (96,95,94). What I
would like is a formula or macro that that would produce the results -
3. There are about 9000 rows of data and I am looking for "count of
new lows within the preceding 10 rows" on a rolling basis.

Thanks.
100
101
96
97
98
97
95
97
94
95

Worksheet version:
First of all, to keep the formula entry simple, insert 9 rows before
your first entry.
In the same column as your first entry, enter the value of the first
entry in all 9 cells. Best way is to select all 9, type the value and,
while holding <ctrl> type <enter>.
I'll use column A as your values, B as a test for minimums and C as the
count of local minimums.
Into B10, enter the formula =IF(A10<MIN(A1:A9),1,0)
Into C10, enter the formula =SUM(B1:B10)
Now copy these from row 11 down to your last value's row.
To clean it up, copy the range from B10 to C18 and then paste those
values back into the same range. Now you can delete rows 1 through 9.
As you add new values to the end of your list, copy the two formulas
down from the preceding row.