Looking for the most profitable range (mathematical names: "maximum subarray problem" or &-CodePudding

To be able to find the most profitable range, I add the lowest value I want to the highest value I want, with that I create a table like this example:

Cell's formulas examples:

Between 0 and 0:

=IFERROR(SUM(FILTER($B$1:$B,($A$1:$A<=D2)*($A$1:$A>=$E$1))))

Between 5 and 10:

=IFERROR(SUM(FILTER($B$1:$B,($A$1:$A<=D12)*($A$1:$A>=$J$1))))

=MAX(E2:O12)
Max Profit = £185.00

=INDEX(A1:O1,ARRAYFORMULA(MAX(IF(E2:O12=MAX(E2:O12),COLUMN(E2:O12)))))
Value Min for Max Profit = 4

=INDEX(D1:D12,ARRAYFORMULA(MAX(IF(E2:O12=MAX(E2:O12),ROW(E2:O12)))))
Value Max for Max Profit = 10

When there are hundreds of values¹ in A and B, this table gets very big and heavy, even causing crashes like my current original data spreadsheet.

Is there any way using a only one formula or script code to found Max Profit | Value Min for Max Profit | Value Max for Max Profit without doing each range one by one needing to use thousands of cells each with a specific formula?

Notes:

hundreds of values¹ → my original spreadsheet currently contains 1471 rows of data in A with the results in B. So to be able to do this analysis, I need to put 2,163,841 formulas like =IFERROR(SUM(FILTER($B$1:$B,($A$1:$A<=D2)*($A$1:$A>=$E$1)))) in the cells to create the table and find the most profitable range.

CodePudding user response：

max:

=INDEX(MAX(IF(SEQUENCE(MAX(A:A) 1)>=SEQUENCE(1, MAX(A:A) 1), 
 SUMIF(SEQUENCE(MAX(A:A) 1), "<="&SEQUENCE(MAX(A:A) 1), B:B)* 
 SEQUENCE(1, MAX(A:A) 1, 1, )-QUERY(QUERY(
 (SEQUENCE(MAX(A:A) 1)<SEQUENCE(1, MAX(A:A) 1))*B1:B, 
 "select "&TEXTJOIN(",", 1, "sum(Col"&SEQUENCE(MAX(A:A) 1)&")")), 
 "offset 1", ), )))

value min:

=INDEX(REGEXEXTRACT(MAX(IF(SEQUENCE(MAX(A:A) 1)>=SEQUENCE(1, MAX(A:A) 1), 
 SUMIF(SEQUENCE(MAX(A:A) 1), "<="&SEQUENCE(MAX(A:A) 1), B:B)* 
 SEQUENCE(1, MAX(A:A) 1, 1, )-QUERY(QUERY(
 (SEQUENCE(MAX(A:A) 1)<SEQUENCE(1, MAX(A:A) 1))*B1:B, 
 "select "&TEXTJOIN(",", 1, "sum(Col"&SEQUENCE(MAX(A:A) 1)&")")), 
 "offset 1", ) (SEQUENCE(1, MAX(A:A) 1)*10^-10)&9, )*1)&"", "0(\d )9$")-1)

value max:

=INDEX(REGEXEXTRACT(MAX(IF(SEQUENCE(MAX(A:A) 1)>=SEQUENCE(1, MAX(A:A) 1), 
 SUMIF(SEQUENCE(MAX(A:A) 1), "<="&SEQUENCE(MAX(A:A) 1), B:B)* 
 SEQUENCE(1, MAX(A:A) 1, 1, )-QUERY(QUERY(
 (SEQUENCE(MAX(A:A) 1)<SEQUENCE(1, MAX(A:A) 1))*B1:B, 
 "select "&TEXTJOIN(",", 1, "sum(Col"&SEQUENCE(MAX(A:A) 1)&")")), 
 "offset 1", ) (SEQUENCE(MAX(A:A) 1)*10^-10)&9, )*1)&"", "0(\d )9$")-1)