count the # of consecutive negative #'s in a range
 

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?


--
newToExcel
------------------------------------------------------------------------
newToExcel's Profile: http://www.excelforum.com/member.php...o&userid=27192
View this thread: http://www.excelforum.com/showthread...hreadid=484569

count the # of consecutive negative #'s in a range
 

Look at this link

http://tinyurl.com/bu9mh

and adapt it to your data

--
Regards,

Peo Sjoblom

(No private emails please)


"newToExcel" wrote
in message ...

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?


--
newToExcel
------------------------------------------------------------------------
newToExcel's Profile:
http://www.excelforum.com/member.php...o&userid=27192
View this thread: http://www.excelforum.com/showthread...hreadid=484569

count the # of consecutive negative #'s in a range
 

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

count the # of consecutive negative #'s in a range
 

thanks a lot guys

i used the 2nd solution as i couldnt get the first one to work


--
newToExcel
------------------------------------------------------------------------
newToExcel's Profile: http://www.excelforum.com/member.php...o&userid=27192
View this thread: http://www.excelforum.com/showthread...hreadid=484569

count the # of consecutive negative #'s in a range
 

Here is a formula, courtesy of Harlan Grove

=MAX(IF(rec<0,COUNTIF(OFFSET(rec,0,COLUMN(rec)-CELL("col",rec),1,-LOOKUP(-CO
LUMN(rec),-LARGE((rec=0)*COLUMN(rec)
+(rec<0)*(COLUMN(rec)+1=CELL("col",rec)+COLUMNS(re c))*(COLUMN(rec)+1),COLUMN
(rec)-CELL("col",rec)+1))-COLUMN(rec)+1),"<0")))

rec is a name for the range of cells, you could substitute the range ref for
rec throughout. It is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"newToExcel" wrote
in message ...

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?


--
newToExcel
------------------------------------------------------------------------
newToExcel's Profile:
http://www.excelforum.com/member.php...o&userid=27192
View this thread: http://www.excelforum.com/showthread...hreadid=484569

count the # of consecutive negative #'s in a range
 

On Sat, 12 Nov 2005 15:19:22 -0600, newToExcel
wrote:


thanks a lot guys

i used the 2nd solution as i couldnt get the first one to work

You're very welcome. Thanks for the feedback.
--ron