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

On Tue, 9 Oct 2007 11:44:01 -0700, dlbeiler
wrote:

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.

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(rn g))))

Longest run <0:

=MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


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

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?

"T. Valko" wrote:

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(rn g))))

Longest run <0:

=MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


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

received the following error message: "#value".
What am I doing incorrectly?

Both formulas are array formulas**.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

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
<0 = 3

screencap:

http://img225.imageshack.us/img225/2...gestrungr2.jpg


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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(rn g))))

Longest run <0:

=MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


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

Which I got using the countmax UDF


Gord

On Wed, 10 Oct 2007 17:39:49 -0400, "T. Valko" wrote:

Based on your sample data I get:

=0 = 5
<0 = 3

Thank you so much for your assistance. It now is working correctly.

"T. Valko" wrote:

received the following error message: "#value".
What am I doing incorrectly?

Both formulas are array formulas**.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

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
<0 = 3

screencap:

http://img225.imageshack.us/img225/2...gestrungr2.jpg


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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(rn g))))

Longest run <0:

=MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"dlbeiler" wrote in message
...
Thank you so much for your assistance. It now is working correctly.

"T. Valko" wrote:

received the following error message: "#value".
What am I doing incorrectly?

Both formulas are array formulas**.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

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
<0 = 3

screencap:

http://img225.imageshack.us/img225/2...gestrungr2.jpg


--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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(rn g))))

Longest run <0:

=MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


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