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 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.
=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.
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.