count the number of new lows within a range of cells

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

0
jbesr1230 (21)
6/1/2006 4:01:41 AM
excel 39879 articles. 2 followers. Follow

2 Replies
545 Views

Similar Articles

[PageSpeed] 17

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


-- 
schoujar
------------------------------------------------------------------------
schoujar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26574
View this thread: http://www.excelforum.com/showthread.php?threadid=547315

0
6/1/2006 5:18:19 AM
jbesr1230 wrote:
> 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.

0
naeyaert (21)
6/1/2006 2:43:34 PM
Reply:

Similar Artilces: