Count maximum consecutive positive numbers

[JP]

In Excel, my challenge is to count the maximum number of consecutive
positive numbers in a column consisting of blank cells and formulas that
evaluate to numbers and "".

For example, starting in A1, the cells evaluate to:

1
-2
3

5
-6

The result would be 2 (counting the two consecutive positive numbers, 3 and
5, above and below the blank or "" cell).

This needs to be done using a single formula or array formula, without using
a separate running total column using =MAX, and without using a UDF. They
say it can't be done and I need to confirm that.

Thank you all,
JP

 

[John]

The below assumes that data is in column 1 and starts row
2 or lower. The answer will be in column 2 1 row above.

The macro replaces the ""'s with 0's to do the calcs and
rhe re-replaces the 0's with ""'s.

John

Sub Macro1()
'
' Macro1 Macro
'
Sheets("sheet1").Activate
Dim rng As Range
Range("b1").EntireColumn.ClearContents
Cells(1, 1).End(xlDown).CurrentRegion.Name = "data"
Range("data").Select
Selection.Replace What:="""""", Replacement:="0",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
toprow = Range("A1").End(xlDown).Row
bottomrow = ActiveSheet.Cells(Rows.Count, _
"A").End(xlUp).Row
start:
Set rng = Sheets("sheet1").Range("data").Find(" ")
If rng Is Nothing Then GoTo continue
rngrow = ActiveSheet.Range("rng").Row
Cells(rngrow, 1).EntireRow.Delete
If rngrow > bottomrow Then GoTo continue
GoTo start
continue:
toprow = Range("A1").End(xlDown).Row
bottomrow = ActiveSheet.Cells(Rows.Count, _
"A").End(xlUp).Row
j = 0
For i = toprow To bottomrow
Cells(i, 1).Select
If Cells(i, 1).Value > 0 Then j = j + 1
If Cells(i, 1).Value = 0 Then j = j
If Cells(i, 1).Value < 0 Then j = 0
Cells(i, 2).Value = j
GoTo nexti
nexti:
Next i
Range("data").Select
Selection.Replace What:="0", Replacement:="""""",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Max = 0
For i = toprow To bottomrow
If Cells(i, 2).Value > Max Then Max = Cells(i, 2).Value
Next i
Cells(toprow - 1, 2).Value = Max
End Sub

 

[Guest]

Just saw I left a bunch of code in that isn't needed! I
had been search for the ""'s but gave up on that idea and
just replaced them.

Section to be removed is noted below.

John

-----Original Message-----
The below assumes that data is in column 1 and starts row
2 or lower. The answer will be in column 2 1 row above.

The macro replaces the ""'s with 0's to do the calcs and
rhe re-replaces the 0's with ""'s.

John

Sub Macro1()
'
' Macro1 Macro
'
Sheets("sheet1").Activate
Dim rng As Range
Range("b1").EntireColumn.ClearContents
Cells(1, 1).End(xlDown).CurrentRegion.Name = "data"
Range("data").Select
Selection.Replace What:="""""", Replacement:="0",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
toprow = Range("A1").End(xlDown).Row
bottomrow = ActiveSheet.Cells(Rows.Count, _
"A").End(xlUp).Row

remove below

start:
Set rng = Sheets("sheet1").Range("data").Find(" ")
If rng Is Nothing Then GoTo continue
rngrow = ActiveSheet.Range("rng").Row
Cells(rngrow, 1).EntireRow.Delete
If rngrow > bottomrow Then GoTo continue
GoTo start
continue:

remove above

 

[Tom Ogilvy]

the OP said:

This needs to be done using a single formula or array formula, without using
a separate running total column using =MAX, and without using a UDF.

Of course I have seen some OP's get all enthusiastic when they received
something they specifically said they didn't want - however, this person
said they were evaluating a "this can't be done in a single formula"
challenge.

Not sure why he posted in programming instead of the more logical
Worksheet.Functions

 

[Harlan Grove]

the OP said:
....
. . . however, this person said they were evaluating a "this
can't be done in a single formula" challenge.

Not sure why he posted in programming instead of the more logical
Worksheet.Functions

Indeed, so I'm crossposting to worksheet.functions. Follow-up there.

Of course it can be done in a single array formula - just barely.

If the data were in a single column by multiple row range named rec, then
try the following.

=COUNT(OFFSET(rec,MATCH(MAX(
MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec)))
-(MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec))),
--((rec<0)*ROW(rec)>=TRANSPOSE(ROW(rec))))>0),1-(rec<0))),
MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec)))
-(MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec))),
--((rec<0)*ROW(rec)>=TRANSPOSE(ROW(rec))))>0),1-(rec<0)),0)
-MAX(MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec)))
-(MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec))),
--((rec<0)*ROW(rec)>=TRANSPOSE(ROW(rec))))>0),1-(rec<0))),0,
MAX(MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec)))
-(MMULT(--(ROW(rec)>=TRANSPOSE(ROW(rec))),
--((rec<0)*ROW(rec)>=TRANSPOSE(ROW(rec))))>0),1-(rec<0))),1))