Forum Discussion

GhostCrab's avatar
GhostCrab
Copper Contributor
Sep 26, 2025

SEQUENCE formula with curly brackets

I have this simple formula, it works as expected up until the SEQUENCE part :

In short rws would equal to 32, and srt to 1

I expected this to output the same as SEQUENCE(32,1,1,1), but it outputs just a single 1

I did check the evaluation process of the formula and it seems like this instead makes SEQUENCE({32},1,{1},1), and that, in a new cell does outputs the same single 1

What is it that i'm missing? Is it because of the LET function? Is there any fix to this?

Thank you in advance!

=LET(
x,E1:E2288,
y,SEQUENCE(ROWS(x)),
z,FILTER(y,x=""),
str,VALUE(INDEX(z,G1)),
rws,VALUE(INDEX(z,G1+1)-str),
SEQUENCE(rws,1,str,1)
)

excel
Formulas and Functions
office 365

4 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 28, 2025

    As an alternative to setting both row and column indices in the INDEX function, you could use the '@' operator to truncate any array output to the value of its initial (scalar) element.

    = LET(
    y,      SEQUENCE(ROWS(x)),
    z,      FILTER(y, x=""),
    str,    @INDEX(z, k),
    rws,    @INDEX(z, k+1)-str,
    return, SEQUENCE(rws,1, str, 1),
    return
  )

    [note: I have used defined names for 'x' and 'k']

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 27, 2025

    Alternatively

    =LET(
   data,  ROW(E1:E2288)/(E1:E2288=""),
   start, AGGREGATE(15,6,data, G1),
   end,   AGGREGATE(15,6,data, G1+1),
   IFERROR( SEQUENCE(end-start,,start),
          "G1 is too big" )
)

    or

    =LET(
   data, TOCOL(ROW(E1:E2288)/(E1:E2288=""),3),
   x,    @CHOOSEROWS(data, G1),
   y,    @CHOOSEROWS(data, G1+1),
   IFERROR( SEQUENCE(y-x,,x),
        "G1 is too big" )
)
  • djclements's avatar
    djclements
    Silver Contributor
    Sep 27, 2025

    To ensure INDEX returns a scalar (TYPE 1 in this case) as opposed to an array (TYPE 64), you need to explicitly set the [column_num] argument to 1, e.g. INDEX(z,G1,1) and INDEX(z,G1+1,1). You can also get rid of VALUE, as it's not needed in this example. ;)

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 27, 2025

    Because your rws always retun 1. This expression INDEX(z,G1+1)-str will always return 1 and you will get final out only one cell from SEQUENCE() function. Would you explain what you want to achieve? Can you put few sample data and show your desired output? Attach a sample file or share via OneDrive or GoolgeDrive or DropBox or similar service.

Resources