On Sat, 12 Nov 2005 13:21:43 -0600, newToExcel
wrote:
hello,
I was wondering if there is a worksheet function that will do this.
e.g. i have a range with some #s like so...
-8 9 7 -8 -9 6 4 8 -8 -9 -2 -3 5 -8 -9 6
what i want is to count the consecutive # of negative #'s in this list.
for the example above the answer will be 4 (-8 -9 -2 -3)
is this possible or do i have to write a macro for this?
There is a simple VBA User Defined Function that can do this.
To enter the function, <alt<F11 opens the
VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.
To use the function, in some cell enter:
=MaxNegCt(rng)
where rng is the range to check.
===========================
Option Explicit
Function MaxNegCt(rg As Range)
Dim c As Range
Dim temp As Long
If rg.Columns.Count 1 And rg.Rows.Count 1 Then
MsgBox ("Must have Single Row or Single Column")
MaxNegCt = CVErr(xlErrRef)
Exit Function
End If
For Each c In rg
If c.Value < 0 Then
temp = temp + 1
Else
If MaxNegCt < temp Then MaxNegCt = temp
temp = 0
End If
Next c
End Function
============================
--ron