Forum Discussion

pbarbosa's avatar
pbarbosa
Brass Contributor
Apr 29, 2024
Solved

Using Array Lambda Formula C(olumns) and R(ows) times in excel formula

After resolving the https://techcommunity.microsoft.com/t5/excel/using-array-formula-c-olumns-and-r-ows-times-in-excel-formula/m-p/4125426   Thanks HansVogelaar and PeterBartholomew1      ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 29, 2024

    pbarbosa 

    Excel doesn't return array of arrays natively. Few variants for the workaround, straightforward one

    =LET(
    ages, B7:INDEX(B7:B1000, Rows),
    sexes, B7:INDEX(A7:A1000, Rows),
    DROP( REDUCE("", SEQUENCE(Rows),
    LAMBDA(a,v,
        VSTACK( a,
            LET(
                lim, maxMT,
                age, INDEX(ages,v,1),
                sex, INDEX(sexes,v,1),
                k₁, Rows,
                k₂, Columns,
                scenario, MOD( SEQUENCE(, k₂, 0), k₂),
                            IF( age + scenario >= lim,
                                0,
                                (Lx_l(age + scenario, sex) -
                                Lx_l(age + scenario + 1, sex)) /
                                Lx_l(age, sex)
                            )
            )
        )
    )
), 1) )

    see in the first sheet attached.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 29, 2024

pbarbosa 

This offers a different solution to the dreaded array of arrays problem.  I broadcast both the age and sex fields across the 2D array of intended results and then used MAP to perform the XLOOKUPs for each cell.

= LET(
    ageArr, age + SEQUENCE(1, cols),
    sexArr, CHOOSECOLS(sex, SEQUENCE(1, cols, 1, 0)),
    (MAP(ageArr - 1, sexArr, Mortalityλ) - MAP(ageArr, sexArr, Mortalityλ)) /
        MAP(age, sex, Mortalityλ)
  )

The Lambda function was simply a matter of working through the code [VLOOKUP returned wrong results for an array but was OK within MAP]

Mortalityλ
= LAMBDA(age,sex,
    LET(
      ageRecord,   XLOOKUP(age, MortalityTbl[Age], MortalityTbl),
      XLOOKUP(sex, MortalityTbl[#Headers], ageRecord)
    )
  )
;
 

Resources