How to find out Max<List<List>> in Excel?-CodePudding

I'd like to find out largest sum of numbers separated by empty row. In this example I am looking to get number 6 (3 3)

Brute forcing this I would =MAX(SUM(A1:A2),SUM(A4:A5),SUM(A7:A8)) which does the job but obviously not practical. How can I express above more elegantly without hardcoding anything?

Thinking out loud, I would like to

Ingest all numbers, split by empty row into some kind of List<List>
Iterate over this list, sum numbers in child list and pick a winner

How can this be done in Excel?

CodePudding user response：

There are multiple ways of doing it, this is just one of them. In cell C1 you can put the following formula:

=LET(set, A1:A9, matrix, 1*TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",
  FALSE, set),",,",";"),",",";", TRUE),m, COLUMNS(matrix), ones, SEQUENCE(m,1,,0),
  MAX(MMULT(matrix, ones))
)

and here is the output:

Note: The third input argument of TEXTSPLIT set to TRUE ensures you can have more than one empty row in the middle of the range. The second input argument of TEXTJOIN set to FALSE is required to ensure to generate of more than one comma (,), which is our condition to replace by the row delimiter (;) so we can split by row and columns. MMULT requires numbers and TEXTSPLIT converts the information into texts. we need to coerce the result into a number by multiplying it by 1.

The formula follows the approach you suggested, you can test the intermediate step. Instead of having as output MAX result the variable you want to verify, for example:

=LET(set, A1:A9, matrix, 1*TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",
  FALSE, set),",,",";"),",",";", TRUE),m, COLUMNS(matrix), ones, SEQUENCE(m,1,,0),
  TMP, MAX(MMULT(matrix, ones)), matrix
)

will produce the following output:

1   1
2   2
3   3

An alternative to MULT is to use BYROW array function (less verbose):

=LET(set, A1:A8, matrix, 1*TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",
  FALSE, set),",,",";"),",",";", TRUE),MAX(BYROW(matrix, LAMBDA(m, SUM(m))))
)