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

Question

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:

1. a series of consecutive worksheet rows were each associated with a spilled 1×n array of numeric values
2. the value of n increased by 1 in successive rows and was utilised within the spill formula for generating that row's array
3. 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

1. One source of difficulty may be the differing value of n in the spilled 1×n arrays
2. 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
3. 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()
4. Attempting to use LET(seq,SEQUENCE(...),BYROW(seq,...)) made no difference.
5. 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?

Answer 1

The short answer is that MAP is a much better choice of function than BYROW in this situation.

The longer answer is that what are called "Try1A", "Try1B" and "Try1C" in the question have the following formulas associated with them:

Try1A: =BYROW(SEQUENCE(7,1,1,1),LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1))))
Try1B: =BYROW(A2#,LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1))))
Try1C: =LET(seq,SEQUENCE(7,1,1,1),BYROW(seq,LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1)))))

A2# is a spill generated by formula =SEQUENCE(7,1,1,1) in cell A2.

Unexpectedly, these 3 formulas do not generate the the same result. The question sought an explanation for this inconsistency.

After an extensive discussion, user @P.b made the suggestion that the MAP function should be used instead of BYROW. This yields the following formulas:

Try1A: =MAP(SEQUENCE(7,1,1,1),LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1))))
Try1B: =MAP(A2#,LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1))))
Try1C: =LET(seq,SEQUENCE(7,1,1,1),MAP(seq,LAMBDA(d,SUM(SEQUENCE(1,d,1+d/10,1)))))

All three formulas yield the same results, namely the expected and correct results shown in the column labelled "Sum" in the image showing in the question.

Further, when the same substitution of MAP for BYROW is made in the remaining 4 "Try"'s of the question, the following formulas result:

Try2A: =MAP(SEQUENCE(7,1,1,1),LAMBDA(d,SUM(MAKEARRAY(1,7,LAMBDA(r,c,IF(c>d,0,c+d/10))))))
Try3A: =MAP(SEQUENCE(7,1,1,1),LAMBDA(d,SUM(EXPAND(SEQUENCE(1,d,1+d/10,1),1,7,0))))
Try3B: =MAP(A2#,LAMBDA(d,SUM(EXPAND(SEQUENCE(1,d,1+d/10,1),1,7,0))))
Try3C: =LET(seq,SEQUENCE(7,1,1,1),MAP(seq,LAMBDA(d,SUM(EXPAND(SEQUENCE(1,d,1+d/10,1),1,7,0)))))

and these 4 formulas again all produce the same correct result, whereas previously they had suffered from the same inconsistency problem displayed by the first 3 when using BYROW.

The reason why BYROW displays its inconsistency still remains a mystery. The only consistent element appears to be that using the function SEQUENCE() twice within the spill formula yields incorrect (ie not the expected) results, whilst using it only once yields correct (expected) results.

MAP does not suffer from this inconsistency and always delivers the correct results irrespective of precisely how the first argument of the MAP function and the second argument of the MAP's LAMBDA are specified.