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

[pbarbosa]

After resolving the https://techcommunity.microsoft.com/t5/excel/using-array-formula-c-olumns-and-r-ows-times-in-excel-f...

Thanks @Hans Vogelaar and @Peter Bartholomew 

I'm looking to expand a lambda formula that can accommodate building scenarios over a span of 5 years (columns) and 8 cases (rows).

I can effectively handle the setup for one row and generate the outcomes across various years.

I can then drag the formula down to address the rest of the cases.

However, the cases might change dynamically, and I need an adaptable and general solution.

Can anyone provide some guidance on this?

I've attached a spreadsheet where I try to explain the concept in more detail.

[#2 · Accepted solution]

@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.

[#3]

@Sergei Baklan 

Very Clear solution. 

At first, I had some doubts about using Drop. I now have to attach other columns with values ​​calculated based on the previous ones. How can I reference a dynamic area (rows and columns)?

For example, append the same number of columns to multiply the previous value (probability of death in the year) by Num (column c).

Thanks for everything once again

[#4]

@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)
    )
  )
;

[#5]

@pbarbosa 

I have just realised that I can halve the number of lookups by returning cumulative values and only then differencing the results.

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

[#6]

@pbarbosa , you are welcome.

Perhaps @Peter Bartholomew answered on your questions, please ask if something is still unclear.

[#7]

@Peter Bartholomew 

@Sergei Baklan 

Your knowledge inspires me. You are within the code. Congratulations.

My next challenge is to calculate, based on the previous array and dynamically, the Death Capital, which is equal to the probability of death x 1000 (for example) x the number of people exposed to the risk (column D, in the test file).

@Peter Bartholomew The cumulative number for year 60 (end -2) appears at the end in orange. Is that just it?

I am sure that mastering these techniques will not allow you to mitigate the risk of errors in the formulas. I need intensive training . Where do you live? I live in Porto, Portugal.

[#8]

@pbarbosa 

Some risks are mitigated.  Others are not.  The problem with single cell (scalar) bottom-up development is that consistency in not assured.  Also the calculation chains tend to be long with many helper cells so it can be difficult to track the overall intent of the calculation.

That said, it is also possible to get a top-down array calculation wrong if the intent of the calculation is misunderstood or the algorithm is flawed.  I try to program Excel so that the assumptions are made as explicit as possible so that a domain expert might be able to compare the formula with their expectations.

Your next steps may require little more than multiplying the result array by the column vector of numbers and allowing the calculation to broadcast the vector over the array.  Similarly multiplication by a factor of 1000 would be applied to every element of the array.  The table that implements this is shown in blue.  Whether it is correct or not depends upon my understanding of your requirement though.

I live 50 miles outside London to the west.  So rather closer than you are to Lisbon?

[#9]

@pbarbosa Another variant for the probability array could be:

=LET(
    data, A7:B14,
    MAKEARRAY(ROWS(data), B4, LAMBDA(r,c, LET(
        age, INDEX(data, r, 2),
        sex, INDEX(data, r, 1),
        IF(age + c - 1 >= maxMT, 0, (Lx_l(age + c - 1, sex) - Lx_l(age + c, sex)) / Lx_l(age, sex))))
    )
)

Alternatively, you could reorganize the Mortality Table in an "unpivoted" format (with columns for "Age", "Sex" and "k"), then use a SUMIFS-based function to spill the results in a 2D array:

=POD(B7:B14, A7:A14, B4)

Where POD is defined in Name Manager as follows:

POD:
=LAMBDA(age,sex,years,
    LET(
        X, LAMBDA(a,s, SUMIFS(TM[k], TM[Age], a, TM[Sex], s)),
        n, SEQUENCE(, years, 0),
        (X(age + n, sex) - X(age + n + 1, sex)) / X(age, sex)
    )
)

You could also add an optional argument to show the accumulated results, if needed:

POD:
=LAMBDA(age,sex,years,[accum],
    LET(
        X, LAMBDA(a,s, SUMIFS(TM[k], TM[Age], a, TM[Sex], s)),
        n, SEQUENCE(, years, 0),
        p, (X(age + n, sex) - X(age + n + 1, sex)) / X(age, sex),
        IF(accum, DROP(SCAN(0, EXPAND(p,, years + 1), LAMBDA(a,v, IF(ISNA(v), 0, a + v))),, -1), p)
    )
)

Then set the optional [accum] argument to TRUE:

=POD(B7:B14, A7:A14, B4, TRUE)

See attached workbooks...