Find the Longest Run.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column of random numbers that are conditionally formatted for the
font to be blue if the number is => 0 and red if <0. How do I find the
longest run of numbers that are => than 0 and the longest run that are < 0
within the column.
 
This UDF will find the longest run of <0 numbers in a column.

Function countmax(MyLetter As String, myRange _
As Range) As Integer
Dim c As Range, TempMax As Integer, _
fReset As Boolean
For Each c In myRange.Cells
If c.Value < 0 Then
TempMax = TempMax + 1
Else
TempMax = 0
End If
countmax = Application.WorksheetFunction _
.Max(countmax, TempMax)
Next
End Function

Usage is........=countmax("<0",A1:A100)

To count >=0 just change the line "If c.Value < 0 Then" to

"If c.Value => 0 Then"

Change formula to =countmax(">=0",A1:A100)


Gord Dibben MS Excel MVP
 
Assuming there are no empty cells in the range...

Both formulas are array formulas**.

Longest run >=0:

=MAX(FREQUENCY(IF(rng>=0,ROW(rng)),IF(rng<0,ROW(rng))))

Longest run <0:

=MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng>=0,ROW(rng))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Thank you for your assistance. The following is the column that I am trying
to solve:
..0005
-.0004
-.0002
..0001
-.0001
..0003
..0001
..0002
-.0003
..0000
-.0003
..0001
-.0005
-.0011
..0007
..0002
..0001
-.0003
..0001
..0006
..0008
..0006
..0007
-.0008
-.0016
..0012
..0006
-.0004
..0000
-.0004
..0007
-.0001
-.0002
..0005
..0028
-.0003
..0003
-.0004
-.0004
-.0002
..0003
..0002
..0004
..0004
..0002
-.0003
-.0003
-.0001

The value that I am looking for is a run of 5 for =>0 and a run of 3 for <0.
I inserted
=MAX(FREQUENCY(IF(J310:J357>=0,ROW(J310:J357)),IF(J310:J357<0,ROW(J310:J357)))) and received the following error message: "#value".
What am I doing incorrectly?
 
received the following error message: "#value".
What am I doing incorrectly?
Click to expand...

You didn't enter the formulas as array formulas.

Type the formula(s) but *don't* hit the ENTER key. Hold down both the CTRL
key and the SHIFT key then hit ENTER. When done properly Excel will enclose
the formula in squiggly braces { }. You can not just type these braces in.
You *must* use the key combination. Also, any time you edit an array formula
you must re-enter it as an array using the key combo.

Based on your sample data I get:
=0 = 5
Click to expand...
<0 = 3

screencap:

http://img225.imageshack.us/img225/2249/longestrungr2.jpg
 
Back
Top