Using name variable from LET produces #VALUE! inside MAP using SUM with the range defined as INDEX : INDEX

Question

I have the following input data (Column 1, 2) an expected output (Column 3, 4). The result is the cumulative sum on each column.

Input	Data	Expected	Result
1	2	1	2
5	9	6	11
3	5	9	16
8	1	17	17

The following formula produces the expected result:

=LET(n, ROWS(A2:B5), m, COLUMNS(A2:B5), 
  rows, MAKEARRAY(n, m, LAMBDA(r,c, r)), cols, MAKEARRAY(n, m, LAMBDA(r,c, c)),
  MAP(rows, cols, LAMBDA(r, c, SUM(INDEX(A2:B5, 1, c):INDEX(A2:B5, r, c)) ))
)

Here is the output:

but if I define the name rng inside LET to avoid repetition in INDEX calls:

=LET(n, ROWS(A2:B5), m, COLUMNS(A2:B5), rng, A2:B5, 
  rows, MAKEARRAY(n, m, LAMBDA(r,c, r)), cols, MAKEARRAY(n, m, LAMBDA(r,c, c)),
  MAP(rows, cols, LAMBDA(r, c, SUM(INDEX(rng, 1, c):INDEX(rng, r, c)) ))
)

Here is the output:

My understanding is that even I am using a name (rng) to represent a range, it is still a range. I am not aware of any limitation on this regard. Is it a bug or am I missing something?

Note: There are multiple ways of achieving the cumulative sum per column, but the question is specific related to the error I found with this approach.

Answer 1

It appears that range variables defined via LET external to the LAMBDA to which they are being passed are first 'evaluated' to their corresponding array.

Since a construction such as:

INDEX(rng,1,c):INDEX(rng,r,c)

requires that rng be a range, and not an array, your current set-up fails, since rng is first being coerced into an array, i.e.:

{1,2;5,9;3,5;8,1}

Moving the LET so as to be internal to the LAMBDA which is calling it resolves this:

=LET(
    n, ROWS(A2:B5),
    m, COLUMNS(A2:B5),
    rows, MAKEARRAY(n, m, LAMBDA(r, c, r)),
    cols, MAKEARRAY(n, m, LAMBDA(r, c, c)),
    MAP(rows, cols, 
    LAMBDA(r, c, LET(rng, A2:B5, SUM(INDEX(rng, 1, c):INDEX(rng, r, c)))))
)

An even simpler example helps support this hypothesis:

=MAKEARRAY(4,1,LAMBDA(r,c,LET(Rng,A2:A5,SUM(INDEX(Rng,1):INDEX(Rng,r)))))

will return

{1;6;9;17}

whereas

=LET(Rng,A2:A5,MAKEARRAY(4,1,LAMBDA(r,c,SUM(INDEX(Rng,1):INDEX(Rng,r)))))

will return

{#VALUE!;#VALUE!;#VALUE!;#VALUE!}