Makearray within formula

[Eppes_A_Yungeman]

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="","",@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)),""))))", but any array made like this doesn't work within other functions. Is there any way to get this formula workable?

[#2]

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

[#3]

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.

[#4]

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)))

[#5]

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.

[#6]

I take that back. you do NOT have to use both variables this also works:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,--a)))

[#7]

Is this a bug or is such a thing is supposed to happen? And if so, why?

[#8]

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

[#9]

That does solve the problem in the original question, but in general is still something I hope is fixed before LAMBDA is released to all Microsoft users.

[#10]

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.

[#11]

i tried a number of variations on your formula (for example:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,IF(a="","",a)+SUMIFS(B5:B7,B5:B7,">="&a))))
and they all seem to work (I don't have your actual data to replicate) That said I don't understand a number of parts of your formula and guess maybe you tweaked it for the example? For example:
IF(a="","",@INDEX(T_ASSETS[STARTING QUANTITY],$B$2)
why at all since a will be 1-10 it will always be false
why the @ in front of the INDEX
why have an INDEX at all because it always pulls the $B$2 element so use a LET and assign that value 1 time and then use the variable instead of having excel index every iteration
SUMIFS(... ,T_ADDRET[EFFECTIVE DATE],"<="&a, ...)
also in this part of the SUMIFS it looks like you are comparing a DATE to the variable 'a' which is a number from 1-10. If they are actually DATEs that won't be true unless you already changed them to be DAY() or something.

[#12]

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)

[#13]

Yes. Half of the formula is cropped out to make it easier to read.
I didn't actually try it on that formula because I am still missing some of the data it's being used on, so I was playing around to try and build as much of the formula as I could, and the first thing I tried was:
=MIN(MAKEARRAY(10,1,LAMBDA(a,b,a)))
which gave me an error, so I didn't even bother trying the whole thing. Being that it is a more complex formula I assume that it won't be an issue in this case.
Thanks for your help.

[#14]

@mtarler 

It looks like another auto-lifting issue with lambdas.

=MIN( 0 + MAKEARRAY(10,1, LAMBDA(a,b,a) ) )

also works.

[#15]

interesting. what is meant by "auto-lifting issue" and when/where else does this happen?

[#16]

@mtarler 

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