Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Describe high/low range with CountIf (ex. count if >90 and <100 | Excel Worksheet Functions | |||
Count formula within a named range. | Excel Discussion (Misc queries) | |||
Ploting data with a large range including negative numbers | Charts and Charting in Excel | |||
Count Consecutive Numbers in a Row | Excel Worksheet Functions | |||
I have a list of dates that I need to count based on a date range | Excel Worksheet Functions |