Using MIN function in dynamic arrays

[zn24]

Hi, I'm trying to figure out the minimum value between two different rows setup as dynamic arrays. I understand using the basic MIN function but when i add the # signs to make it dynamic it doesn't work.

I wondering how I can use the min function within a dynamic array. Thank you.

 

[Cubist]

We'd need more information. What is the formula you're using? Can you provide an example?

 

[zn24]

We'd need more information. What is the formula you're using? Can you provide an example?

Sure ofcourse. So I'm trying to choose the minimum between two values

I used the formula MIN(J327#,J329#) and it returns this to me

(66,196,505,848,262,900,000,000,000,000,000,000,000,000,000,000,000,000,000,000.0)

Once I remove the # signs it works normal for the cell its in but then it doesn't spill.

Thank you.

LNC - Zainab Nwachuku.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
322	Operating cashflow								284.3	290.3	344.7	399.7	460.7
323	Investing cashflow								(225.0)	(250.0)	(275.0)	(275.0)	(275.0)
324	Manadatory debt repayments								(75.0)	(75.0)	(75.0)	(75.0)	(75.0)
325	Share issuance/buybacks								(150.0)	(140.0)	-	-	-
326	Dividend payouts								(41.2)	(49.8)	(59.6)	(69.4)	(80.1)
327									(206.8)	(224.4)	(64.9)	(19.8)	30.6
328
329	Opening balance								-	#########	#########	#########	#########
330	Issuance/repayment								(2,089,296,208,544,980,000,000,000,000,000.0)
331	Closing balance							0.0	(2,089,296,208,544,980,000,000,000,000,000.0)	#########	#########	#########	#########
332	Interest expense			5.25%					-	#########	#########	#########	#########
333
334	Net interest expense								53.1	#########	#########	#########	#########
Model

Cell Formulas
Range	Formula
J322:N322	J322	=J105#
J323:N323	J323	=J112#
J324:N324	J324	=J309#+J315#
J325:N325	J325	=J273#+J280#
J326:N326	J326	=-(J276#+J284#)
J327:N327	J327	=J322#+J323#+J324#+J325#+J326#
I331	I331	=I155
E332	E332	=Assumptions!N29
J329:N329	J329	=IFS(J9#=start,I331,J9#>start,SUMIFS(J331:N331,J9#,J9#-1))
J330	J330	=MIN(J327#,J329#)
J331:N331	J331	=J329#+J330#
J332:N332	J332	=E332*J329#
J334:N334	J334	=J311#+J317#+J332#-J304#
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
start	=Assumptions!$F$9	J329

 

[RoryA]

Once I remove the # signs it works normal for the cell its in but then it doesn't spill.

MIN is not going to spill - it will give you the (single) lowest value for all the cells in those two ranges. You probably need something like BYCOL or MAP so that you can get the min value for each column.

 

[Cubist]

You can try something like this maybe..

Book1
	A	B	C	D	E	F	G	H	I	J
4	1	2	3	4	5	6	7	8	9	10
5	10	9	8	7	6	5	4	3	2	1
6
7	1	2	3	4	5	5	4	3	2	1
Sheet4

Cell Formulas
Range	Formula
A4:J4	A4	=SEQUENCE(1,10)
A5:J5	A5	=SEQUENCE(1,10,10,-1)
A7:J7	A7	=IF(A4#<A5#,A4#,A5#)
Dynamic array formulas.

 

[zn24]

MIN is not going to spill - it will give you the (single) lowest value for all the cells in those two ranges. You probably need something like BYCOL or MAP so that you can get the min value for each column.

Thank you so much I used the BYCOL formula and it works perfectly. =BYCOL(J327:N328,LAMBDA(column,MIN(column)

 

[zn24]

You can try something like this maybe..

Book1
	A	B	C	D	E	F	G	H	I	J
4	1	2	3	4	5	6	7	8	9	10
5	10	9	8	7	6	5	4	3	2	1
6
7	1	2	3	4	5	5	4	3	2	1
Sheet4

Cell Formulas
Range	Formula
A4:J4	A4	=SEQUENCE(1,10)
A5:J5	A5	=SEQUENCE(1,10,10,-1)
A7:J7	A7	=IF(A4#<A5#,A4#,A5#)
Dynamic array formulas.

Thank you so much I used the BYCOL formula then wrapped it up with an IF statement to account for the negative figures turned positive, like this IF(J327#<0,-J327#,BYCOL(J327:N328,LAMBDA(column,MIN(column)