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=""...
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)))
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?
- I wonder if the values being passed by MAKEARRAY to LAMBDA are not actually numbers but a pointer or something and hence if not converted using 0+a or --a etc... some functions throw an error. So going back to your original equation:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,IFERROR(IF(a="","",@INDEX(T_ASSETS[STARTING QUANTITY],$B$2)+SUMIFS(T_ADDRET[QUANTITY],T_ADDRET[TYPE],"Add",T_ADDRET[EFFECTIVE DATE],"<="&a,T_ADDRET[ASSET ID],$C$2)),""))))
what happens if you replace the &a in the SUMIFS to be & --a