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 between the minimum and maximum numbers in the range. It works properly. when I try to replace A19:A31 with FILTER(A:A,ISNUMBER(A:A)) to include all cells in the range A:A, I only get the first cell and not a sequence. when I look in the "evaluate formula" option, the "start" parameter is evaluated in curly brackets. for example, if the first cell is 10000, the parameter is {10000}.
How can I "cast" the curly brackets into regular number?
I would suggest a LET() solution.
=LET( a,FILTER(A:A,ISNUMBER(A:A)), b,MIN(a), c,MAX(a), d,c-b+1, SEQUENCE(d,,b))- 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).- 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?
