Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
May 25, 2021
Solved

Let function used with Index.

Hi,   I try to figurer out how to use the new LET function, in this example wich has the same INDEX function several times.    Is it possible, if yes, aht is the solution?    Example file is at...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 25, 2021

Hogstad_Raadgivning 

This is just a 'cobbled' together solution.  There are standard functions which scan a function over an array and collect the results together.  The problem is that they are only standard if you have a functional programming background and I need time to study to acquire such.  What I did was to wrap the previous formula in a lambda function so that it becomes a single function call, e.g.

= ModelBestλ(5)

= LAMBDA(k,
      LET(
        Found,XLOOKUP(INDEX(Modell, k), t_priser[#Headers],t_priser),
        BestPrice, SMALL(Found,1),
        Utileier, IF({1,0}, t_priser[Utlleier], Found),
        XLOOKUP(SMALL(Found,1),Found,Utileier))
    )

I then set up a recursion passing through the indices in decreasing order.

= ForEachλ(5,ModelBestλ)

= LAMBDA(p,fλ,
    LET(
       mb, fλ(p),
       result, IF(p>1, ForEachλ(p-1,fλ), mb),
       IF(p>1, xStackλ(result, mb), mb) )
   )

 Note, the function to calculate the best price for each model is passed to this function as a parameter.  The other thing I needed was a function to add the new 'model best' to the list of results already accumulated.  For this I wrote xStackλ

= xStackλ({1,2;3,4},{5,6})

= LAMBDA(prior,new,
     LET(
        n, ROWS(prior),
        k, SEQUENCE(n+1),
        IF( k<=n, INDEX(prior,k,{1,2}), INDEX(new,{1,2})))
   )

As I suggested at the beginning, this needs considerable tidying up if it is to respond to adding model types dynamically or to be reusable for classes of related problems.

 

Resources