Curious behaviour of BYROW(SEQUENCE(...),...) construct

Background

In recent work I attempted to use a construct of BYROW(SEQUENCE(...),...) to create a column of results. The results were not what I expected. As a result, I investigated and came across some curious findings - set out below. Whilst I now know what works and what doesn't for this construct, I am not sure of the underlying reasons for this. Is anyone able to give an explanation for what I have found?

The original work had the following characteristics:

a series of consecutive worksheet rows were each associated with a spilled 1×n array of numeric values
the value of n increased by 1 in successive rows and was utilised within the spill formula for generating that row's array
the result of interest for each row was a single value obtained by wrapping the row's spill formula within the SUM() function.

When calculating each row's value using a formula for a single cell there was no problem.

The difficulties arose when attempting to exploit the characteristics of n to deliver a column of results using a BYROW(SEQUENCE(...),...) construct in which

the SEQUENCE() function generated the successive values of n and
the LAMBDA() (used as the second argument of BYROW()) recreated the spill formula used to generate the 1×n array and its summation to a single value.

Findings of Investigation

One source of difficulty may be the differing value of n in the spilled 1×n arrays
Not every solution involving constant sized arrays produced the correct result: a solution involving MAKEARRAY() gave the correct result whilst one involving EXPAND() gave incorrect results
Replacing the SEQUENCE() argument in BYROW(SEQUENCE(...),...)with a reference to a worksheet range produced correct results, even when that worksheet range was itself generated by the same SEQUENCE() used in the BYROW()
Attempting to use LET(seq,SEQUENCE(...),BYROW(seq,...)) made no difference.
No solution was found to work if it contained two occurrences of SEQUENCE() within it.

Details of Investigation

The investigation is based on a simplified version of the original work. It retains the characteristics of the original and, importantly, reproduces the difficulties found in that work.

The top half of the screenshot, range A1:H8, provides the data used. The "Number" column provides the values called n in the Background section. The remaining columns in the top half, labelled "SEQUENCE(1,Number,1+Number/10,1)" provide the spilled 1×n arrays. The formula for cell B2 is

=SEQUENCE(1,A2,1+A2/10,1)

and this is copied to range B3:B7.

Note: Use of SEQUENCE() to generate the 1×n arrays leads, in several of the attempted solutions to this function being used twice: first, as the first argument of BYROW(); and, second, within the LAMBDA() used as the second argument of BYROW(). The issue raised in this question is primarily about the first of these two uses.

The bottom half of the screenshot provides the correct results in column "Sum" and shows 7 different attempts to reproduce these results using a spilled array formula (in columns "Try1A" to "Try3C").

The formula for cell A11 is

=SUM(SEQUENCE(1,A2,1+A2/10,1))

and this is copied to range A12:A17.

The formulas in cells A11:A17 make no reference to any of the spilled arrays in the top half of the screenshot. Range B2:H8 can be deleted entirely without affecting the results in the bottom half. Columns B:H in the top half are provided only for illustration and to help understand which results in the bottom half are correct and incorrect.

The initial attempt at using a spilled formula is shown in column "Try1A". The formula in cellB11 is

=BYROW(SEQUENCE(7,1,1,1),LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1))))

This does not produce the expected results (ie those in column "Sum"). Instead it reproduces the values in range B2:B7, the first column obtained when all 7 of the 1×n arrays are "stacked" leftwards and vertically.

Suspecting that it might be the variable sizes of the spilled arrays within the LAMBDA() of "Try1A", "Try2A" used MAKEARRAY() to generate constant sized arrays of size 1×7. The formula in cell E11 is:

=BYROW(SEQUENCE(7,1,1,1),LAMBDA(d,SUM(MAKEARRAY(1,7,LAMBDA(r,c,IF(c>d,0,c+d/10))))))

This worked and delivered the correct results.

Encouraged by this "Try3A" also generated 1×7 arrays but this time using EXPAND(). The formula in cell F11 is

=BYROW(SEQUENCE(7,1,1,1),LAMBDA(d,SUM(EXPAND(SEQUENCE(1,d,1+d/10,1),1,7,0))))

but this delivered the same incorrect result as "Try1A".

Finally, two variants of the incorrect formulas used in "Try1A" and "Try3A" were tested. In the first variant, the first argument of the BYROW() function was replaced with A2:A8 resulting in formulas of:

"Try1B" cell C11: =BYROW(A2:A8,LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1))))
"Try3B" cell G11: =BYROW(A2:A8,LAMBDA(d,SUM(EXPAND(SEQUENCE(1,d,1+d/10,1),1,7,0))))

Both worked and continued to work when range A2:A8 was itself created using the spill formula of

=SEQUENCE(7,1,1,1) in cell A2

and the references to A2:A8 replaced by the spill version of A2#

"Try1C" and "Try3C" built on the first variant by using the LET() function to replace direct references to the worksheet. The resulting formulas are:

"Try1C" cell D11: =LET(seq,SEQUENCE(7,1,1,1),BYROW(seq,LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1)))))
"Try3C" cell H11: =LET(seq,SEQUENCE(7,1,1,1),BYROW(seq,LAMBDA(d,SUM(EXPAND(SEQUENCE(1,d,1+d/10,1),1,7,0)))))

However, both delivered the same incorrect result as "Try1A".

To repeat from the start of this long question:

Whilst I now know what works and what doesn't for this construct of BYROW(SEQUENCE(...),...), I am not sure of the underlying reasons for this. Is anyone able to give an explanation for what I have found?

Solution

The BYROW function applies a LAMBDA function to each row of a given array or range. As such, each iteration is referencing an entire row, even if the array only contains a single column.

When using =BYROW(SEQUENCE(7), LAMBDA(r, ...)), the SEQUENCE(7) function returns an array object consisting of {1;2;3;4;5;6;7}. While you might expect the r variable to return a single numeric value from 1 to 7 for each row in the array, it actually returns another array object consisting of a single value from {1} to {7}. When the resulting array is then passed to either the rows or columns argument of the subsequent SEQUENCE function, Excel interprets this as an attempt to generate an array of arrays (a separate set of results for each value in the array), which is currently not supported. As such, only the first result will be returned. For example, SEQUENCE(1, {5}) will only return 1.

When using =BYROW(A2#, LAMBDA(r, ...)), where cell A2 contains the formula =SEQUENCE(7), the correct results are returned because A2# is a range object referring to a single column that exists in the worksheet. Each row in the range contains a single cell only; and, when a single cell is referenced, its value is returned (ie: 5 instead of {5}).

Alternatively, the MAP function applies a LAMBDA function to each value of a given array. When using =MAP(SEQUENCE(7), LAMBDA(n, ...)), the n variable does in fact return a single numeric value from 1 to 7, because it processes each item in the array individually. This is why the MAP function can be a better option than BYROW or BYCOL when working with 1D arrays.

Having said that, there are ways of overcoming the described issue with BYROW when working with a single column array. The INDEX function, as well as the implicit intersection operator @, can be used to reference the first item in each row and return a single value. For example, the Try1A formula mentioned in the OP can be modified to work as follows:

=BYROW(SEQUENCE(7), LAMBDA(r, LET(n, INDEX(r, 1), SUM(SEQUENCE(, n, 1 + n / 10)))))

-OR-

=BYROW(SEQUENCE(7), LAMBDA(r, SUM(SEQUENCE(, @r, 1 + @r / 10))))

Note: when using the implicit intersection operator method shown above, you may be prompted with the following message:

This formula is not supported by some older versions of Excel.

Would you like to use this variation instead?

=@BYROW(SEQUENCE(7), LAMBDA(r, SUM(SEQUENCE(, @r, 1 + @r / 10))))

Click No to proceed.