Trying to find the longest chain of nonblank cells in Google Sheets-CodePudding

I'm trying to figure out the best/simplest way to get the desired outcome from a data set which looks like the images. Cells A2:A40 are all autoincrementing by 1. When a value is deleted, a blank is introduced and a new autoincrement starts.

I would like to find a way to functionally program cell A1 to parse down the length of A2:A(last cell), and each time there is a blank, it will run a counta() and then it will take the max value of all those counts, and return the max streak. So if I decide to delete the value in cell A16, the new longest counta() would be 9 (from c28).

I don't need to see the values of each count (col C), I would just like to return the max() in A1.

Thanks,

CodePudding user response：

Try this ARRAYFORMULA into cell A1:

=ARRAYFORMULA(MAX(FREQUENCY(IF(A2:A40<>"",ROW(A2:A40)),IF(A2:A40="",ROW(A2:A40)))))

CodePudding user response：

Try

=if(A1="",MATCH(1,arrayformula(if(A2:A="",1,0)),0)-1,)