How to return the max number of consecutive positive cells in a column?-CodePudding

Looking to count the maximum/max/largest number of consecutive cells with positive or negative numbers in a range / column.

This resource describes a method that seems to work well in most cases, but for the particular sheet where I need it to work, it returns a number far too large to be correct.

For Negative:

=arrayformula(MAX(FREQUENCY(IF(Daily!P5:P<0,ROW(Daily!P5:P),""),IF(Daily!P5:P>=0,ROW(Daily!P5:P),""))))

For Positive:

=arrayformula(MAX(FREQUENCY(IF(Daily!P5:P>0,ROW(Daily!P5:P),""),IF(Daily!P5:P<=0,ROW(Daily!P5:P),""))))

Here is a copy of the spreadsheet. Sheet 1 holds the formulas in various ways. The first two versions show you how the formulas work with a test range. The second two versions show the formula used for the Actual Data. This is where the problem occurs. For negative numbers, it works. But for positive numbers, it shows 68 consecutive numbers, and there aren't even 68 consecutive numbers in the range.

I'm wondering if this problem is the result of some kind of idiosyncrasy with Google Sheets and how these functions are used.

CodePudding user response：

this is caused by your formula in B column. change:

=IF(ISBLANK($A5), "", IF(NetGross="Gross", 
 SUMIF(Date, $A5, GrossReturnDollar), SUMIF(Date, $A5, NetReturnDollar)))

to this:

=IF(ISBLANK($A5),, IF(NetGross="Gross", 
 SUMIF(Date, $A5, GrossReturnDollar), SUMIF(Date, $A5, NetReturnDollar)))

or:

=IF(ISBLANK($A5), IF(,,), IF(NetGross="Gross", 
 SUMIF(Date, $A5, GrossReturnDollar), SUMIF(Date, $A5, NetReturnDollar)))