2

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Axuary
  • 1,497
  • 1
  • 4
  • 20
  • So, what is your expected output then? I think you can use Max with index and sequence function. – Harun24hr Apr 10 '21 at 18:02
  • @Harun24HR I have tried many variations of `MAX` and `INDEX`. I haven't found one that works with arrays that aren't tied to cell references. – Axuary Apr 10 '21 at 18:53
  • Just verifying; Why is the desired outcome 12, 8, 9? Because it's the max of each of those columns? – JvdV Apr 10 '21 at 19:02
  • 1
    I'm still thinking of something but meanwhile maybe try: `=LET(X,A1:C3,CHOOSE({1,2,3},MAX(INDEX(X,,1)),MAX(INDEX(X,,2)),MAX(INDEX(X,,3))))` – JvdV Apr 10 '21 at 19:10
  • @JvdV Thanks for that. I am looking for the max of each column and that looks like it would work for a 3x3. The ultimate problem I am trying to solve is a 36x36. Two cells would be much easier for that. My fault for not including that bit of information. – Axuary Apr 10 '21 at 19:29

2 Answers2

5

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))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • 1
    Thank you! Now where did I get that idea from originally? https://stackoverflow.com/questions/66953423/how-to-add-total-line-dynamic-array-formula-in-excel/66955679#66955679 – Tom Sharpe Apr 11 '21 at 09:12
  • 1
    Thank you. I was working on something like this and couldn't get it to work. I adapted this to the handle any size array. `=LET(x,A1:D4,r,ROWS(x),sa,SEQUENCE(COUNT(x),1,0),sb,SEQUENCE(1,r)*r,col,INDEX(x,MOD(sa,r)+1,INT(sa/r)+1),sortcol,SORTBY(col,INT(sa/r),1,col,1),INDEX(sortcol,sb))` – Axuary Apr 11 '21 at 12:40
3

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)

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Thank. Lots of new information here. Did not know about `ARRAYTOTEXT`. I will have to look at it some more. I have only really looked at `LAMBDA` out the current insider functions. It looks like I need to learn more about `FILTERXML`. I only really know how to do one thing with it. – Axuary Apr 10 '21 at 21:56
  • You are welcome. About the `FILTERXML()` part, you can find more information on the function and some possible usages [here](https://stackoverflow.com/q/61837696/9758194). – JvdV Apr 11 '21 at 08:52
  • Btw, I did come up with a much shorter `LAMBDA()` function but I won't be able to test since it seems currently `LAMBDA()` isn't working on my end. Even previously working formulas I wrote don't seem to work so I'll probably have to wait for the next update. – JvdV Apr 11 '21 at 09:03