Feb 18 2023 10:59 AM
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?
Feb 18 2023 11:24 AM
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))
Feb 18 2023 12:03 PM
Feb 18 2023 10:32 PM
Feb 18 2023 11:05 PM
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().