Use of LARGE, SMALL, MAX, MIN, SUM Functions in spilled arrays

[AEAA]

Hi all,

I would like to use functions like LARGE, SMALL, MAX, MIN and SUM in spilled arrays and get the values PER row instead of the total. For Example:

x	y	=MAX(A2:B2) dragged down	=MAX(A2:B5)
1​	2​	2​	8​
3​	4​	4​
5​	6​	6​
7​	8​	8​

The only solution I have found online is by using a mix of SUBTOTAL and OFFSET, but I would like to avoid having volatile functions.

=SUBTOTAL(4,OFFSET(INDEX(Rng,1,1),ROW(Rng)-MIN(ROW(Rng)),,,COLUMNS(Rng)))

This works, but Rng needs to exists in the spreadsheet, if instead of Rng I have an array defined inside of the equation it returns #VALUE!. Meaning I cannot do multiple operations within one single equation.

You help would be appreciated! Thanks.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	1	2	2	1
3	3	4	4	3
4	5	6	6	5
5	7	8	8	7
Main

Cell Formulas
Range	Formula
C2:C5	C2	=BYROW(A2:B5,LAMBDA(r,MAX(r)))
D2:D5	D2	=BYROW(A2:B5,LAMBDA(r,MIN(r)))
Dynamic array formulas.

 

[AEAA]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	1	2	2	1
3	3	4	4	3
4	5	6	6	5
5	7	8	8	7
Main

Cell Formulas
Range	Formula
C2:C5	C2	=BYROW(A2:B5,LAMBDA(r,MAX(r)))
D2:D5	D2	=BYROW(A2:B5,LAMBDA(r,MIN(r)))
Dynamic array formulas.

Hi Fluff, thank you very much for the quick response.

I am not familiar with these BYROW and LAMBDA functions, and my excel does not seem to recognize them.

I am using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20850) 64-bit . Do I need to activate something?

 

[Fluff]

You can try checking for updates, as that release number seems a bit old.
Other than that I think you are stuck with a drag down formula or Offset.

 

[Eric W]

Depending on your data, you can sometimes come up with a trick to do it. For example:

Book1
	A	B	C	D	E
1				Max by row	Min by row
2	2	1		2	1
3	3	4		4	3
4	5	5		5	5
5	7	8		8	7
Sheet9

Cell Formulas
Range	Formula
D2:D5	D2	=MOD(SMALL(A2:B5+ROW(A2:B5)*100,SEQUENCE(4,,2,2)),100)
E2:E5	E2	=MOD(SMALL(A2:B5+ROW(A2:B5)*100,SEQUENCE(4,,,2)),100)
Dynamic array formulas.

This assumes that the values in columns A:B are between 1 and 99, and that there are 2 columns. But I suspect a basic drag-down formula would be clearer and safer.

 

[AEAA]

You can try checking for updates, as that release number seems a bit old.
Other than that I think you are stuck with a drag down formula or Offset.

My last update was on March, 2022 and the newest update does not include either the BYROW or LAMBDA functions. I read a bit about them and they are only available in the Office Insider component that for some reason is not available in Microsoft 365 Apps for enterprise. I think they will add the function very soon.

In any case, I saw what the function does and it does seem to solve the problem entirely. I will mark it as a possible solution! Thanks for the help.

 

[Fluff]

The Lambda function is on general release, but only on the Monthly Channel. You will probably get it in July when the next update for the semi-channel comes out.

Did you try what Eric suggested?

 

[AEAA]

Depending on your data, you can sometimes come up with a trick to do it. For example:

Book1
	A	B	C	D	E
1				Max by row	Min by row
2	2	1		2	1
3	3	4		4	3
4	5	5		5	5
5	7	8		8	7
Sheet9

Cell Formulas
Range	Formula
D2:D5	D2	=MOD(SMALL(A2:B5+ROW(A2:B5)*100,SEQUENCE(4,,2,2)),100)
E2:E5	E2	=MOD(SMALL(A2:B5+ROW(A2:B5)*100,SEQUENCE(4,,,2)),100)
Dynamic array formulas.

This assumes that the values in columns A:B are between 1 and 99, and that there are 2 columns. But I suspect a basic drag-down formula would be clearer and safer.

The Lambda function is on general release, but only on the Monthly Channel. You will probably get it in July when the next update for the semi-channel comes out.

Did you try what Eric suggested?

I am not very familiar with monthly and semi-channel, as my company is handling all the software updates. But yeah I guess I'll have to wait until July (I guess it's the Semi-annual enterprise channel I see my About Excel)

Yes, I am trying it right now and the logic is very nice. Only problem is that ROW() needs to have a defined Array existing in the sheet and my array is generated inside of the formula.

However, I have found a nice workaround to it. I have done:

SMALL(Rng+SEQUENCE(ROWS(Rng);;1000;1000);SEQUENCE(10;;COLUMNS(Rng);COLUMNS(Rng)))-SEQUENCE(ROWS(Rng);;1000;1000)

This is a bit more flexible and dynamic, with the only problem that all values in the Rng have to be values, ERRORS and Empty cells ruin the logic. AGGREGATE(15;6... does not return a spill array unfortunately.

Thanks to Eric for the suggestion!