Please help me calculate the maximum consecutive negative value in a column

a column will have positive and negative numbers. I need to find a run of consecutive negative numbers that returns the largest combined negative value.

attached is an example. Column A is the data. Column B is the desired output.

Thanks!!!

attached is an example

sorry, no attachment

One way. In B1, copied down:

=SUM(INDEX(A:A,AGGREGATE(14,6,ROW($A$1:A1)/($A$1:A1>0),1)+1):A1)

then =min(B:B)

these were the questions I was going to ask about different scenarios...
1) row 17 has -1, row 18 has -1023 and row 19 has 61... what then? pull data from rows 17 and 18 - in other words find the lowest number and pull the next consecutive one above or below that is also negative?
2) row 17 has 61, row 18 has -1023 and row 19 has 2... pull numbers from rows 11 through 15 or just pull the lowest negative # even if it is sitting alone?
3) row 17 has -2, row 18 has -1023 and row 19 has -1 and row 20 has -57, keep pulling as long as you've found the lowest negative and include the entire consecutive sequence above and below that also contains negatives? in other words, let's say rows 11 through 22 were all negatives and -1023 was still in the mix, you want the sum of all those included?

But Glenn's formulas take care of all those issues so I think you've got your answer.

I'm fooling around with MMULT, trying to avoid the helper... but without success so far.

By ANY chance... are you using O365 yet? If so, it's easy to lose the helper column:

=LET(A,A2:A23,B,IF(A>=0,0,A),C,DROP(VSTACK(0,B),-1),MIN(MAP(B,C,LAMBDA(y,z,IF(z=0,0,z+SUM(y))))))

Yes, SCAN would be better (shorter)... but I still can't do away with the helper for Excel 2021.

It is possible.

Formula:

Please Login or Register  to view this content.

Oh POOH. It doesn't work. Back to the drawing board!!!!