# Iterating a lambda function over an array

Copper Contributor

# Iterating a lambda function over an array

Hello,

I have added what I hope is a simplified version of the problem I am having here. Take this pair of matrices:

The function I need is expected to behave as follows: Suppose I am interested in searching both matrices for the coordinate row=1, col=1, and summing the results, and if no result is found, returning 0. If we search for row=1, col=1, we find such a coordinate in matrix A, with a corresponding cell value of 1.00. In matrix B, so such coordinate is found, so a 0 is returned. The sum yields a 1.00. I have such a function, but it involves writing it out for EACH such matrix, when I would like to do only once. Further details follow.

I have a function that searches matrix A and B for a particular row/col index (coordinate pair), and sums the result. Here is the formula, where, in this case,  it searches for the rows (the 4 numbers beneath each matrix letter) for 1, then the columns (the 4 numbers to the left of each matrix letter) for 1, then sums the resulting intersection, returning 0 when a given coordinate is not found. Note here that F30 is the A cell, and F36 is the B cell:

``=local_search(1,1,F30)+local_search(1,1,F36)``

Where local search itself is as follows:

``=LAMBDA(r,c,m,IFERROR(XLOOKUP(c,OFFSET(m,,-4):OFFSET(m,,-1),XLOOKUP(r,OFFSET(m,1,0):OFFSET(m,4,0),OFFSET(m,1,-1):OFFSET(m,4,-4))),0))``

Where r is the row parameter, c is the column, and m is the reference cell, denoted by that capitalized letter cell. The issue I am having is that I do not want to write out that local search function for every matrix I search. It seems to me the only parameter that is changing (at least for when looking for a given coordinate pair) is the reference cell, and I have been unable to write a formula that iterates local_search over an array of such reference cells sums each result. The reference cells are in a single column, every 6 rows.

If I can successfully find a technique for doing so over just matrix A and B, I am confident I can extrapolate over any number of matrixes (I'd eventually like about 20 or so matrices to be searched).

Thanks again,
Tony

Previous version of the problem:

I have a Lambda function (local_search) that searches a matrix for the presence of a given row/col combination, and if found, returns that result, and if not, returns a 0. It has three parameters, r, c, and m, where r is the row index, c is the column index, and m is the matrix reference cell. For reference, here it is

``=LAMBDA(r,c,m,IFERROR(XLOOKUP(c,OFFSET(m,,-4):OFFSET(m,,-1),XLOOKUP(r,OFFSET(m,1,0):OFFSET(m,4,0),OFFSET(m,1,-1):OFFSET(m,4,-4))),0))``

Now this formula is used within another lambda, which applies local_search to a series of such matrices and uses the results to populate a larger one. For example, the result of each row x, col y combination is summed, and in the following manner (where the reference cells are in F30, F36, and F42):

``=LAMBDA(x,y,local_search(x,y,F30)+local_search(x,y,F36)+local_search(x,y,F42))``

The problem I am having is that as I expand the table to a larger number of matrixes to apply local_search to, I keep largely copy-pasting local_search, with only the m parameter changing. It seems to me as if one occurrence of local_search iterating over some array of m, and summing the results, would be more efficient. I am hoping to have something more like this:

``=SUM(POWER(INDEX(F:F,{30,36,42}),2))``

Where the power function iterates over each array element and sums the result. I tried using ChatGPT to aid me in this quest to no avail. I have no idea if this is possible, but I feel as if it likely is, and easily so.

Thanks all again; I am brand new to Excel and am looking forward to learning from all of you. I have attempted to attach the Excel file itself but do not appear able to. I have attached some screenshots that I hope will illuminate the problem. As it stands, the formulas and tables were perfectly fine; they are just inefficient.

Regards,

Tony

11 Replies

# Re: Iterating a lambda function over an array

Perhaps some sample data with expected results would be useful?

Regards

# Re: Iterating a lambda function over an array

Share a sample workbook (attach file to your post) with desired output. REDUCE() function may help with VSTACK().

# Re: Iterating a lambda function over an array

Thank you for the quick reply Harun. Pardon the simple question, but how does one attach an Excel file? I tried and it said that the excel file type was not supported, or that I could not attach it anyway. Thanks again,

# Re: Iterating a lambda function over an array

Looks like MAP() function is what you are looking for.
A screenshot of the data with the expected result will be fine if you can't attach the file.

# Re: Iterating a lambda function over an array

I have attached screenshots. If more is required, please let me know. A bit annoying I cannot seem to add the worksheet itself. Thanks igorot.

# Re: Iterating a lambda function over an array

If you are new to Excel, you most certainly have not entered at the ground floor!

You appear to have a vertical array of matrices, each 4x4 with a column header above and a row header to the right.  The best approach is probably to treat the entire gold coloured range containing the matrices as a single entity and address each matrix by index, rather than a cell reference.

The stride length from matrix to matrix is 6 so either the rows to jump in DROP would be

(elementIndex-1)*6

or, if you do not have TAKE and DROP, you could still use OFFSET but relative to the top-left gold cell.  With the modern functions, you would use TAKE to pick up the 4 rows of the local stiffness matrix, otherwise declare the height and width of the element matrix to be 4 in OFFSET.

The overall formula would use MAP to pick each element in turn to return the value from each and return the SUM of the values it looks up.

# Re: Iterating a lambda function over an array

re:the formulas and tables were perfectly fine; they are just inefficient.

what do you mean inefficient?

# Re: Iterating a lambda function over an array

Hey Peter,
I have added a simpler variant of the problem at the start of my post. I tried a few different versions of a MAP formula that might yield results, to no success thus far. I suspect I need to express 'm' (my matrix parameter) as some type of array, but online I've only found various curly bracket expressions that I think might be applicable to older versions of Excel (I use 365). Thanks again for your time.

# Re: Iterating a lambda function over an array

I have tried to clarify the problem with a new introduction to my original post. My version of the formula only works by writing out the local_search function for each instance of a matrix I want to search, when I would prefer to only write out this search function once, and iterate it over a list of matrices to be searched.

# Re: Iterating a lambda function over an array

Hi. I have created a Recursive Lambda Function.

Give it a try if this one meets your requirement.

``````SuMatrix = LAMBDA(m, nMax,
LET(
rslt, LET(
arr, OFFSET(m, 1, -1):OFFSET(m, 4, -4),
idX, OFFSET(m, 0, -1):OFFSET(m, 0, -4),
idY, OFFSET(m, 1, 0):OFFSET(m, 4, 0),
MAKEARRAY(
nMax,
nMax,
LAMBDA(r, c, IFNA(INDEX(arr, XMATCH(r, idY), XMATCH(c, idX)), 0))
)
),
IF(ISTEXT(OFFSET(m,6,0)), SuMatrix(OFFSET(m,6,0),nMax) + rslt, rslt)
)
);``````

``=SuMatrix(m, nMax)``

Where m is the first cell reference of your matrices, in the below example is cell F30.

nMax is the maximum index you have.

I'm just too lazy to copy all the values in the example/photo you provided.LOL

# Re: Iterating a lambda function over an array

This goes for the problem somewhat at a single hit and could possibly benefit from a little more modularisation using Lambda functions.

``````=LET(
nodeTable,    TAKE(WRAPROWS(TOROW(stiffnessTable), 30), , 4),
elementIndex, SEQUENCE(ROWS(nodeTable)),
MAKEARRAY( 10, 10,
LAMBDA(rowIndex, columnIndex,
LET(
rowFreedom,  BYROW(nodeTable, LAMBDA(nodeList, XMATCH(rowIndex, nodeList))),
colFreedom,  BYROW(nodeTable, LAMBDA(nodeList, XMATCH(columnIndex, nodeList))),
unfiltered,  HSTACK(elementIndex, rowFreedom, colFreedom),
criterion,   BYROW(IFNA(unfiltered, 0), LAMBDA(f, AND(f))),
returnValue, IF(
criterion,
INDEX(stiffnessTable, 6 * (elementIndex - 1) + rowFreedom + 1, colFreedom)
),
SUM(returnValue)
)
)
)
)``````

It uses a calculated index applied to the entire list of stiffness matrices.  By the way, how do you plan to invert the matrix equations?