max consecutive "criteria" cells in a range

[daniel proville]

hello, I would like to do the following:
listing positive or negative numbers in a column,which represen
trading results(wins or losses), I am considering a function tha
outputs the maximum number of consecutive positive cells in that colum
and also the maximum number of consecutive negative cells.
what would be the simplest approach ?
thanks in advance
danie

 

[Ron Rosenfeld]

hello, I would like to do the following:
listing positive or negative numbers in a column,which represent
trading results(wins or losses), I am considering a function that
outputs the maximum number of consecutive positive cells in that column
and also the maximum number of consecutive negative cells.
what would be the simplest approach ?
thanks in advance
daniel

Here are two VBA functions to do that:

==============================
Function MaxPos(rng As Range) As Long
Dim c As Range
Dim l As Long

For Each c In rng
If c.Value > 0 Then l = l + 1
If c.Value < 0 Then
MaxPos = Application.WorksheetFunction.Max(MaxPos, l)
l = 0
End If
Next c

End Function

Function MaxNeg(rng As Range) As Long
Dim c As Range
Dim l As Long

For Each c In rng
If c.Value < 0 Then l = l + 1
If c.Value > 0 Then
MaxNeg = Application.WorksheetFunction.Max(MaxNeg, l)
l = 0
End If
Next c

End Function
============================

--ron

 

[daniel proville]

thank you ron, efficient & simple
I appreciate.
cheers
daniel

 

[Ron Rosenfeld]

thank you ron, efficient & simple
I appreciate.
cheers
daniel

You're welcome.


--ron

 

[Leo Heuser]

Hello Daniel

Here's one way using a helper column (B)

Assuming
listing in A2:A100
B1 must be empty or contain text.

In B2 enter
=IF((SIGN(A2)=SIGN(A3)),SUM(B1)+SIGN(A2),0)

Copy B2 down to B100

Max. number of concecutive positive cells:

=MAX(B2:B100)+(COUNTIF(A2:A100,">0")>0)


Max. number of concecutive negative cells:

=ABS(MIN(B2:B100))+(COUNTIF(A2:A100,"<0")>0)

 

[Aladin Akyurek]

Consider the following sample in A1:A22...

{"X";6;-4;6;6;1;6;-7;-9;9;3;5;-9;-1;7;4;7;-10;-6;-8;0;-2}

In B2 enter:

=SIGN(A2)

In B3 enter & copy down:

=IF(SIGN(A3)<>SIGN(A2),SIGN(A3),LOOKUP(9.99999999999999E+307,$B$1:B2)+SIGN(A
3))

For the maximum number of consecutive positive cells, use:

=MAX(B2:B22)

For the maximum number of consecutive positive cells, use:

=MIN(B2:B22) or =ABS(MIN(B2:B22))

 

[Leo Heuser]

Aladin

It may not be a problem here, but for all positive
or all negative values, your Max and Min formulae
don't return the correct result.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Consider the following sample in A1:A22...

{"X";6;-4;6;6;1;6;-7;-9;9;3;5;-9;-1;7;4;7;-10;-6;-8;0;-2}

In B2 enter:

=SIGN(A2)

In B3 enter & copy down:

3))

For the maximum number of consecutive positive cells, use:

=MAX(B2:B22)

For the maximum number of consecutive positive cells, use:

=MIN(B2:B22) or =ABS(MIN(B2:B22))

 

[Aladin Akyurek]

Leo,

The formula considers 0 as a break in a sequence. That is, 0 is neither
positive nor negative. It marks the end of the previous sequence and the
start of the next.

However, with all >0 or <0, we need to adjust the MAX and MIN formulas:

=MAX(B2:B22,0)
=MIN(B2:B22,0) or =ABS(MIN(B2:B22,0))

Aladin

Aladin

It may not be a problem here, but for all positive
or all negative values, your Max and Min formulae
don't return the correct result.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.