Find maximum value in each row or column of an Excel array that is not a cell reference

user_14385419 0
Viewed 380

I need to find the maximum of a column in an array inside of a LET call that is not a cell reference using one cell.

This works for arrays that are cell references but when I try to use it on an array that is not a cell reference, it fails.

For example, using this data.

12  2  3
 3  7  5
 7  8  9

=LET(Rng,$A$1:$C$3,SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),,COLUMN(Rng)-MIN(COLUMN(Rng)),ROWS(Rng)))) returns [12, 8, 9] as you would expect but =LET(Rng,$A$1:$C$3*1,SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),,COLUMN(Rng)-MIN(COLUMN(Rng)),ROWS(Rng)))) returns #VALUE

Is this possible to do in a single cell or am I forced to use multiple cells?

Edit: Desired output is

12  8  9

I have a formula that works for arrays that are tied to cells, I don't have one that works for arrays that are not tied to cells, like the result of a calculation within a LET

Edit 2: Ultimately I need something that works for a 36x36 array.

office365 dynamic-arrays excel-formula excel
2 Answers

Here's an alternative approach using more conventional indexing and sorting:

=LET(sa,SEQUENCE(9,1,0),sb,SEQUENCE(1,3,3,3),col,INDEX(A1:C3,MOD(sa,3)+1,QUOTIENT(sa,3)+1),
sortcol,SORTBY(col,QUOTIENT(sa,3),1,col,1),INDEX(sortcol,sb))

The idea is to convert the 2d array into a 1d array (col), then sort it first by the column number in the original array, then by the values in the array. Finally extract every third element from the resulting array. This shows the steps separately:

enter image description here

This is the overall result:

enter image description here

I forgot that the point of the question was that it should work for an array as well as a range:

=LET(sa,SEQUENCE(9,1,0),sb,SEQUENCE(1,3,3,3),col,INDEX(A1:C3*1,MOD(sa,3)+1,QUOTIENT(sa,3)+1),
sortcol,SORTBY(col,QUOTIENT(sa,3),1,col,1),INDEX(sortcol,sb))

or

=LET(sa,SEQUENCE(9,1,0),sb,SEQUENCE(1,3,3,3),col,INDEX(RANDARRAY(3,3),MOD(sa,3)+1,QUOTIENT(sa,3)+1),
sortcol,SORTBY(col,QUOTIENT(sa,3),1,col,1),INDEX(sortcol,sb))

General form for max of columns of rectangular array (e.g. 4 rows by 3 columns)

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(1,c,r,r),col,
INDEX(arr,MOD(sa,r)+1,QUOTIENT(sa,r)+1),sortcol,SORTBY(col,QUOTIENT(sa,r),1,col,1),INDEX(sortcol,sb))

For min, just change sort order:

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(1,c,r,r),col,
INDEX(arr,MOD(sa,r)+1,QUOTIENT(sa,r)+1),sortcol,SORTBY(col,QUOTIENT(sa,r),1,col,-1),INDEX(sortcol,sb))

General form for max of rows of rectangular array

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(r,1,c,c),col,
INDEX(arr,QUOTIENT(sa,c)+1,MOD(sa,c)+1),sortcol,SORTBY(col,QUOTIENT(sa,c),1,col,1),INDEX(sortcol,sb))

Again for min just change sort order:

=LET(arr,A1:C4*1,r,ROWS(arr),c,COLUMNS(arr),sa,SEQUENCE(r*c,1,0),sb,SEQUENCE(r,1,c,c),col,
INDEX(arr,QUOTIENT(sa,c)+1,MOD(sa,c)+1),sortcol,SORTBY(col,QUOTIENT(sa,c),1,col,-1),INDEX(sortcol,sb))
user_38949170

Alright, this is a long stretch and very experimental. I feel there could be a better way. For the following to work I used the function ARRAYTOTEXT() which I believe only is available for users with access to Microsoft365's insiders programm. Nonetheless, here is my attempt:

enter image description here

Formula in E1:

=LET(X,ARRAYTOTEXT(TRANSPOSE(A1:C3),1),TRANSPOSE(FILTERXML("<y><t><s>"&SUBSTITUTE(SUBSTITUTE(MID(X,2,LEN(X)-2),",","</s><s>"),";","</s></t><t><s>")&"</s></t></y>","//s[not(.< preceding-sibling::*)][not(.<= following-sibling::*)]")))

You can test the working on an array by replacing the A1:C3 reference to something like RANDARRAY(4,4,1,100,1)

user_97581940
Related