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.

Possible 'duplicate' [here](https://stackoverflow.com/a/74007255/9758194) — JvdV, Nov 28 '22 at 06:27
@JvdV Yes, though re one of your main conclusions in that post - "*the moment you assign it to the variable it becomes an array*", I believe my post presents a refinement of that statement. — Jos Woolley, Nov 28 '22 at 07:22
Yes, very good @JosWoolley. David Leal; closed your question due to dupe reasons but I do believe you have all information now you need right? If you feel it's not, let me know and we can re-open. — JvdV, Nov 28 '22 at 08:08
It is fine @JvdV because this question was posted after the other one and the issue is the same (It didn't show up as a similar question), but the answer provided by @JosWolley addresses better the root cause of the problem. The discovery here by Jos is that any name variable defined in an 'external' `LET` is considered as an array (even it is range) inside of an array function that uses a `LAMBDA` such as `MAP` and maybe other similar ones. The **only difference** from the first question, is identifying that the problem happens also with `MAP` function not just with `MAKEARRAY`. — David Leal, Nov 28 '22 at 14:14

Jos Woolley · Accepted Answer · 2022-11-28T06:08:00.427

4

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!}

edited Nov 28 '22 at 06:08

answered Nov 28 '22 at 06:02

Jos Woolley

8,564
2
4
9

Thanks @JosWolley, it seems to be reasonable explanation. I think it is a bug or a non documented constraint in the new array functions. I also tested this: `=LET(x,3,rng, A2:B5, MAP(x, LAMBDA(item, SUMIF(rng, "<"&item))))` but now I get a `#CALC!` error instead. Doing it in the way you suggested: `=LET(x,3, MAP(x, LAMBDA(item, LET(rng, A2:B5, SUMIF(rng, "<"&item)))))`returns `4`. So the type of error is not even consistent – David Leal Nov 28 '22 at 13:58
Adding just another approach provided in a different forum for further reference: [MAXIFS doesn't work inside MAP using names from LET](https://techcommunity.microsoft.com/t5/excel/maxifs-doesn-t-work-inside-map-using-names-from-let/m-p/3681425#M171470). It is also documented here in another question I posted that seems to have the same root cause: [MAXIFS doesn't work as expected invoked inside MAP using names from LET](https://stackoverflow.com/questions/74510691/maxifs-doesnt-work-as-expected-invoked-inside-map-using-names-from-let) – David Leal Dec 05 '22 at 20:58

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

1 Answers1

Linked