Feb 23 2023 11:13 PM - edited Feb 24 2023 03:53 PM
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
Feb 23 2023 11:32 PM
Feb 24 2023 12:26 AM
Feb 24 2023 01:17 AM
Feb 24 2023 03:04 AM
Feb 24 2023 09:43 AM
Feb 24 2023 12:08 PM
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.
Feb 24 2023 02:58 PM
Feb 24 2023 03:46 PM
Feb 24 2023 03:48 PM
Feb 24 2023 09:45 PM
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
Feb 25 2023 06:34 AM
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?