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?

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.

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

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

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 errorbut if you let it spill and then in another cell you use that MIN or other function it works fineI 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)))

Makearray within formula | Microsoft Community Hub

mtarler
Silver Contributor
Jun 17, 2022
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
- Eppes_A_Yungeman
  Copper Contributor
  Jun 17, 2022
  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.
  - mtarler
    Silver Contributor
    Jun 17, 2022
    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)))

Forum Discussion

Makearray within formula

Share

Resources