Can't pass a range to a lambda using Let and Makearray functions

Question

Referring back to this question, I wish to create a running total in an array, but starting afresh in each row of the array


1	2	3	4
5	6	7	8

resulting in


1	3	6	10
5	11	18	26

One solution is to use Makearray and this works fine:

=MAKEARRAY(
    2,
    4,
    LAMBDA(r, c,
        SUM(
            INDEX(sheet1!A1:D2, r, 1) : INDEX(sheet1!A1:D2, r, c)
        )
    )
)

However I now wish to write this more generally using a Let statement:

=LET(
    range, Sheet1!A1:D2,
    MAKEARRAY(
        rows(range),
        Columns(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)

but it results in

This very similar formula works correctly (it isn't supposed to solve the original problem, but is just included to test whether a range can be passed into a lambda inside a Let statement):

=LET(
    range, Sheet1!A1:D2,
    SCAN(0, range, LAMBDA(a, c, a + c + INDEX(range, 1, 1)))
)

The same code also works passing range as a parameter into a named function in Google Sheets.

Defining range as a named range also works.

Using an enclosing Lambda instead of Let like this has the same issue in Excel:

=LAMBDA(range,
    MAKEARRAY(
        ROWS(range),
        COLUMNS(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)

I've named it TestLambda and called it as =TestLambda(A1:D2)

But this works in Google Sheets:

=LAMBDA(range,
    MAKEARRAY(
        ROWS(range),
        COLUMNS(range),
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)(A1:D2)

Can there be a specific issue with Makearray or is there another reason why this fails?

I don't get the errors with `=LET(range,A1:D2,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,SUM(INDEX(A1:D2,r,1):INDEX(A1:D2,r,c)))))` https://i.stack.imgur.com/mwqwZ.jpg do you have a named range equal to rows or columns maybe? I notice your formula shows `rows(range)` and `Columns(range)` not in caps, where expected. — P.b, Oct 09 '22 at 17:14
Thanks both @p.b you've repeated the original range a1:d2 explicitly inside the lambda instead of replacing it with 'range' so that would explain the difference. — Tom Sharpe, Oct 09 '22 at 18:11
`Using an enclosing Lambda instead of Let has the same issue.` Does it? At least in Google sheets, `LAMDA`(no `LET`) arguments are passed by ``reference`` and not by ``value``. So, `INDEX():INDEX()` should work with `LAMBDA` — TheMaster, Oct 17 '22 at 14:06
Interesting. I didn't test that very thoroughly so shall go back to it. — Tom Sharpe, Oct 17 '22 at 14:17
I have just checked in Excel and I was correct, it does have the same issue. Will add code to my answer. — Tom Sharpe, Oct 17 '22 at 19:09
I've named it TestLambda and called it as =TestLambda(A1:D2) — Tom Sharpe, Oct 17 '22 at 19:18
FWIW, `:` is the range reference operator, as described [here](https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a). That's why it only applies to ranges — chris neilsen, Oct 18 '22 at 00:25

JvdV · Accepted Answer · 2022-10-09T19:02:33.033

7

The construct of INDEX() >>:<<INDEX() will work when applied to ranges. Not to arrays AFAIK. It will lead to these errors. Maybe try something like:

=LET(range,A1:D2,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,SUM(INDEX(range,r,SEQUENCE(c))))))

This would resemble the construct of your inital formula. However, in the linked question you have mentioned that you'd like to use SCAN() in combination with BYROW(). You have noticed that nesting these lambda helper functions will result in an error. I just wanted to show you that it is possible with just a single SCAN() function:

=SCAN(0,A1:D2,LAMBDA(a,b,IF(COLUMN(b)=1,b,a+b)))

Or even less verbose:

=SCAN(0,A1:D2,LAMBDA(a,b,a*(COLUMN(b)<>1)+b))

I have been puzzling a bit and found that we could compare the row/column of the 'b' variable inside the lambda structure. Something I haven't been aware of prior to....now. It does feel a bit glitchy but now we can actually use some sort of variation to the BYROW() helper inside SCAN() and do these calculation for each row of the intial array seperately.

edited Oct 09 '22 at 19:02

answered Oct 09 '22 at 18:00

JvdV

70,606
8
39
70

Definitely a good answer because it works but I'm still puzzled because range _is_ a range . Or am I missing something - is it actually passed as an array to the lambda? – Tom Sharpe Oct 09 '22 at 18:09
@TomSharpe, the moment you assign it to the variable it becomes an array, therefor the `INDEX()` trick becomes obsolete. Again, this is AFAIK and my experience goes. It may become clearer if one would not name the variable 'range' but 'arr' instead. Another thing that could have thrown you off is the `ROWS()` and `COLUMNS()` functions that one would usually use on an actual range. However, these functions *will* work on an array. Try `=ROWS(1;2;3}`. – JvdV Oct 09 '22 at 18:10
1

Ah, I understand now. It must be buried inside the documentation somewhere or does that piece of knowledge just come from experience? – Tom Sharpe Oct 09 '22 at 18:16
1

@TomSharpe, That is based purely on trial and error. Note: I updated my answer with a take just using `SCAN()`. Something you did attempt yourself answering the linked question. – JvdV Oct 09 '22 at 18:53
1

It does seem remarkable that you can test the column of the b variable as you have done. Congratulations for making that discovery! – Tom Sharpe Oct 09 '22 at 19:27
@TomSharpe, would be nice to see if we could find a non-volatile version to apply this to columns. I pieced together `=SCAN(0,A1:D2,LAMBDA(a,b,IFERROR(OFFSET(b,-1,0)+b,b)))` so far. – JvdV Oct 09 '22 at 20:09
I'm not sure if it works beyond two rows? No, I would be stuck on that one because Scan can only scan across not down. – Tom Sharpe Oct 09 '22 at 21:14
Nice @JvdV the column version above would sum the current row's value to the previous row's value, but would not take the calculated b value like what typically habbens in `SCAN`. Would transposing it and transposing it back make that possible? – P.b Oct 09 '22 at 21:18
@TomSharpe yup it appears we can't find a way (yet) to refer to the vertical predecessor in its own column. As p.b. mentioned a transpose would probably work but gets more lengthy again. Will keep puzzling – JvdV Oct 10 '22 at 05:18
2

The only problem with transposing is that we then lose the possibility to interrogate the resulting array's row/column index, since transposing results in an array, not a range, and so standard functions such as `ROW` and `COLUMN` are not possible. – Jos Woolley Oct 10 '22 at 05:22
1

@JosWoolley, you are correct that it will stop working, but it's contradictory to what I mentioned in the 2nd comment here since these functions actually do work on arrays. For example `=COLUMN(TRANSPOSE(A1:D2))` correctly equals 2. It seems that it will however stop working inside `SCAN()`. Bummer. – JvdV Oct 10 '22 at 05:33
1

My 2nd attempt using `OFFSET()` (yes volatile) `=SCAN(0,A1:D2,LAMBDA(a,b,IFERROR(SUM(OFFSET(b,-ROW(b)+1,0,ROW(b))),b)))`. – JvdV Oct 10 '22 at 05:37
@JvdV You can enter `=COLUMN(TRANSPOSE(A1:D2))` on your version of Excel? – Jos Woolley Oct 10 '22 at 06:09
I certainly can't, just as my machine won't allow, for example, `COLUMN({1,2,3,4;5,6,7,8})`, or any other non-range passed to that function. – Jos Woolley Oct 10 '22 at 06:11
@JosWoolley. Sorry. Meant Columns. Lost in Translation – JvdV Oct 10 '22 at 06:30
1

@JvdV your 2nd attempt works on multiple columns. Nice work! – P.b Oct 10 '22 at 15:37
Just to add a reference to a question labeled as duplicate: [Using name variable from LET produces #VALUE! inside MAP using SUM with the range defined as INDEX : INDEX](https://stackoverflow.com/questions/74595902/using-name-variable-from-let-produces-value-inside-map-using-sum-with-the-rang) where @JosWoolley provided a good explanation about the error and how to avoid it defining the names inside `MAKEARRAY` function. – David Leal Dec 05 '22 at 20:33

Can't pass a range to a lambda using Let and Makearray functions

1 Answers1

Linked

Related