Forum Discussion

SilverStat's avatar
SilverStat
Copper Contributor
Feb 24, 2023

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

  • SilverStat 

    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?

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    re:the formulas and tables were perfectly fine; they are just inefficient.

    what do you mean inefficient?
    • SilverStat's avatar
      SilverStat
      Copper Contributor
      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.
  • SilverStat 

    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.

    • SilverStat's avatar
      SilverStat
      Copper Contributor
      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.
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Share a sample workbook (attach file to your post) with desired output. REDUCE() function may help with VSTACK().
    • SilverStat's avatar
      SilverStat
      Copper Contributor
      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,
      • FikturFox's avatar
        FikturFox
        Brass Contributor
        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.

Resources