Forum Discussion
Makearray within formula
I am trying to get the minimum value in an array that is generated using a formula. I would like to do this in one cell using MAKEARRAY, something like "=MIN(MAKEARRAY(10,1,LAMBDA(a,b,IFERROR(IF(a=""...
so this is very interesting and I don't know what exactly is happening.
if you don't use either of the variable (a or b) in the equation then you get that error
but if you let it spill and then in another cell you use that MIN or other function it works fine
I tried using LET() to 'separate' the steps and even tried using the min outside the let so MIN(LET(...)) and it still gives the error.
The only solution I found is to use BOTH variables in the equation. For example:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,a+0*b)))
if you don't use either of the variable (a or b) in the equation then you get that error
but if you let it spill and then in another cell you use that MIN or other function it works fine
I tried using LET() to 'separate' the steps and even tried using the min outside the let so MIN(LET(...)) and it still gives the error.
The only solution I found is to use BOTH variables in the equation. For example:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,a+0*b)))
I take that back. you do NOT have to use both variables this also works:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,--a)))
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,--a)))