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=""...
it doesn't seem to be the MAKEARRAY or LAMBDA functionality. I tried this and it worked fine:
=MIN(MAKEARRAY(22,11,LAMBDA(a,b,a+b)))
I would look into your calculation steps
=MIN(MAKEARRAY(22,11,LAMBDA(a,b,a+b)))
I would look into your calculation steps
- That example does work, but:
=MAKEARRAY(10,1,LAMBDA(a,b,a))
which spills the array {1;2;3;4;5;6;7;8;9;10}, when put into the MIN function:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,a)))
returns #N/A.
This is not only with the MIN function, it also happened with any other function I tried that takes multiple arguments in the form of arrays (MAX, AVERAGE, SUM, PRODUCT, ETC.). This does not always happen when more complex formulas are used to generate the array, and therefore seems to be an issue with MAKEARRAY itself or the LAMBDA within it.- 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)))- I take that back. you do NOT have to use both variables this also works:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,--a)))
