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=""...
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
=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
I don't remember what exactly I did but I once got an error that was something like "nested arrays not allowed". I wonder if this issue is related to that.
More about auto-lifting is here Yellow (lambdadays.org), "Auto-lifting/coercion" slide.
Usually it doesn't work in combination of some "old" functions and dynamic arrays, for example
Sometimes with named formulae. To force it, if reference adding of plus +range helps, if an array add something to it as here with +0
- interesting. what is meant by "auto-lifting issue" and when/where else does this happen?
It looks like another auto-lifting issue with lambdas.
=MIN( 0 + MAKEARRAY(10,1, LAMBDA(a,b,a) ) )also works.
- so A) LAMBDA is fully released
B) "nested arrays not allowed" is something else
C) we should send Feedback (File -> Feedback -> I don't like something)
