Forum Discussion
Formula evaluated in curly brackets
in cells A19:A31 I have numbers in sorted order. I've applied this formula: =SEQUENCE(ROUND(ABS(MIN(A19:A31)-MAX(A19:A31)),0)+1,,ROUND(INDEX(A19:A31,1),0),1) in order to create a sequence of numbers ...
Let's suppose that I want to complicate things little bit:
The numbers are now not sorted, and it's okay.
What I want to do next is to create a sequence with "d" rows, but the "start" parameter now needs to be the first cell in "a" (regardless if it's the minimum or maximum in the range).
I've updated your formula into this form:
=LET( a,FILTER(A:A,ISNUMBER(A:A)), b,MIN(a), c,MAX(a), d,c-b+1,firstNumInRangea,INDEX(a,1), SEQUENCE(d,,firstNumInRangea))
Still, I only get the first cell in "a" and not a sequence. My guess would be that the SEQUENCE function is treating the "start" parameter as an array, and therefore can't choose which cell in the array to apply (even though the size of the array is 1).
The numbers are now not sorted, and it's okay.
What I want to do next is to create a sequence with "d" rows, but the "start" parameter now needs to be the first cell in "a" (regardless if it's the minimum or maximum in the range).
I've updated your formula into this form:
=LET( a,FILTER(A:A,ISNUMBER(A:A)), b,MIN(a), c,MAX(a), d,c-b+1,firstNumInRangea,INDEX(a,1), SEQUENCE(d,,firstNumInRangea))
Still, I only get the first cell in "a" and not a sequence. My guess would be that the SEQUENCE function is treating the "start" parameter as an array, and therefore can't choose which cell in the array to apply (even though the size of the array is 1).
I've managed to make it work, but I don't understand the mechanism behind it.
Apparently, if I use SUM around the INDEX function, it does what it is intended to do.
Any ideas why this is happening, and how to use it more wisely than using SUM function?
Apparently, if I use SUM around the INDEX function, it does what it is intended to do.
Any ideas why this is happening, and how to use it more wisely than using SUM function?
Usually N() resolves such problems. But not in this case.
I suppose it has to do with the dynamic array inside INDEX().
It works if you use a range-reference instead.
It works if you use helper cells instead of LET().
