Forum Discussion
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
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?
- re:the formulas and tables were perfectly fine; they are just inefficient.
what do you mean inefficient?- 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.
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.
- 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.
- Share a sample workbook (attach file to your post) with desired output. REDUCE() function may help with VSTACK().
- 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,
- 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.
