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 30, 2024

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
  )
pbarbosa's avatar
pbarbosa
Brass Contributor
Apr 30, 2024

PeterBartholomew1 

SergeiBaklan 

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

 

PeterBartholomew1 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 :lol:. Where do you live? I live in Porto, Portugal.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 01, 2024

    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?

Resources