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 inte...
Excel on Mac
Formulas and Functions
SilverStat's avatar
SilverStat
Copper Contributor
Feb 24, 2023
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
Feb 24, 2023
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.
  • SilverStat's avatar
    SilverStat
    Copper Contributor
    Feb 24, 2023
    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.
    • FikturFox's avatar
      FikturFox
      Brass Contributor
      Feb 25, 2023

      SilverStat 

      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

Resources