excelexcel-formularangepass-by-referencelet# Can't pass a range to a lambda using Let and Makearray functions

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?

Solution

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.

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel