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