Finding uninterrupted sub-arrays in Excel - Kadane's algorithm variation?

I can see smarter folk than me looking at this but the below would be one way to isolate the discrete sums of the 1 to n arrays, against which you could apply threshold logic.

Formula:

cell of choice

=SUBTOTAL(9,OFFSET($B$1,AGGREGATE(15,6,ROW($B$2:$B$13)/(($B$2:$B$13<>0)*(N(+$B$1:$B$12)=0))-1,ROW(A$1:INDEX($A:$A,SUMPRODUCT(($B$2:$B$13<>0)*($B$1:$B$12=0))))),0,1+AGGREGATE(15,6,ROW($B$1:$B$12)/(($B$2:$B$13=0)*(N(+$B$1:$B$12)<>0)),ROW(A$1:INDEX($A:$A,SUMPRODUCT(($B$2:$B$13<>0)*($B$1:$B$12=0)))))-AGGREGATE(15,6,ROW($B$2:$B$13)/(($B$2:$B$13<>0)*(N(+$B$1:$B$12)=0)),ROW(A$1:INDEX($A:$A,SUMPRODUCT(($B$2:$B$13<>0)*($B$1:$B$12=0))))),1))

so, applying an outer MAX to the above, coupled with a final >= test would give you the boolean response [would need to be confirmed with CTRL + SHIFT + ENTER]

edit: I can't vouch for Google Sheets feasibility as I don't use it, I'm afraid.

Not particularly happy about the double-OFFSET, but as a first attempt, based on data in A1:B13 (with headers in row 1), array formula**:

=MAX(SUBTOTAL(9,OFFSET(OFFSET(B2,A2:A14,),,,-FREQUENCY(IF(B2:B13,A2:A13),IF(B3:B14=0,A2:A13,0))-1)))

Note the deliberately offset ranges (A2:A13, A2:A14, B2:B13, B3:B14).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Edit: don't need the double-OFFSET after all!

=MAX(SUBTOTAL(9,OFFSET(B2,A2:A14,,-FREQUENCY(IF(B2:B13,A2:A13),IF(B3:B14=0,A2:A13,0))-1)))

Regards

With my formula you cannot replace SUBTOTAL with SUM, no. You could have a particular set of values for which both versions give identical results, I suppose, though that would be pure coincidence.

For the original data you posted, for example:

=MAX(SUBTOTAL(9,OFFSET(B2,A2:A14,,-FREQUENCY(IF(B2:B13,A2:A13),IF(B3:B14=0,A2:A13,0))-1)))

returns 15, whereas:

=MAX(SUM(OFFSET(B2,A2:A14,,-FREQUENCY(IF(B2:B13,A2:A13),IF(B3:B14=0,A2:A13,0))-1)))

returns 11.

Unlike SUM, SUBTOTAL can return an array of sums, which can then be passed to a further function, e.g. MAX.

Regards

Problematic!

I'll have a look and see if I can find an alternative construction that works in Google Sheets.

I have to say that Sheets' lack of an equivalent to the Excel Evaluate Formula tool is a huge oversight, especially when working with complex constructions such as this one.

Regards

The issue appears to lie not only with the FREQUENCY function but also with how OFFSET functions within Sheets.

If FREQUENCY were the only issue, hard-coding the correct output from that function should theoretically work. However:

=ArrayFormula(MAX(SUBTOTAL(9,OFFSET(B2,A2:A14,,{-1;-1;-1;-1;-1;-5;-1;-1;-1;-1;-3;-1;-1}))))

returns #VALUE!.

I believe I've come across this issue before, though don't recall finding a satisfactory explanation.

Regards

Ah, ok. It's because, in Sheets, OFFSET's height parameter must be positive.

In fact, it's supposed to be in Excel as well!

"Height Optional. The height, in number of rows, that you want the returned reference to be. Height must be a positive number."

(From https://support.office.com/en-us/art...e-b4d906d11b66.)

It appears that a long-standing 'bug' allows negative values for this parameter in Excel:

https://answers.microsoft.com/en-us/...b-68b599b31bf5

First I knew! I've used this property on several occasions.

Obviously Google fixed this bug in their equivalent.

Will continue to look for an alternative 'non-buggy' solution!

Cheers

Hi,

I've managed to come up with another Excel-based solution which this time does not use FREQUENCY. However, it does use OFFSET and, again, it fails in Sheets due to the apparent difference in functionality of that function across the two platforms.

I've posted a thread re OFFSET in Google Sheets here:

https://www.excelforum.com/for-other...arameters.html

I'm afraid that, unless someone can confirm that it is in fact possible to pass arrays to OFFSET's parameters within Google Sheets, I doubt that I will be able to provide you with a solution which works there.

Regards