May 25 2021 07:35 AM
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 attached.
Best regards
- Geir
May 25 2021 07:56 AM
SolutionAfraid that won't work without lambdas since required array of arrays. If only @Peter Bartholomew knows some trick.
May 25 2021 07:58 AM
May 25 2021 08:16 AM
May 25 2021 09:04 AM
May 25 2021 09:29 AM
May 25 2021 10:34 AM
If the objective is to examine the use of the LET function, the following demonstrates the way in which a solution may be built using formula-local names
= LET(
Found,XLOOKUP(@Modell,t_priser[#Headers],t_priser),
BestPrice, SMALL(Found,1),
Utileier, IF({1,0}, t_priser[Utlleier], Found),
XLOOKUP(SMALL(Found,1),Found,Utileier)
)
The additional trick is to combine the seller and their prices for the vehicle type into a table and then perform the second lookup to select the best offering.
If the entire table were required as a single spilt range, that would need a plunge into LAMBDA to perform the array of array calculations and collect the results for each.
May 25 2021 10:55 AM
May 25 2021 11:00 AM
I meant one spill for all results is required
May 25 2021 02:09 PM
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.
May 25 2021 07:56 AM
SolutionAfraid that won't work without lambdas since required array of arrays. If only @Peter Bartholomew knows some trick.