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.
=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))) - I tried that as well. I did find that adding 0 to one of the variables also works:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,a+0)))
It seems that the LAMBDA requires an equation to work as a nested array, though I can't figure out why that would happen.- Is this a bug or is such a thing is supposed to happen? And if so, why?