Forum Discussion
LAMBDA Halloween Challenge
Finally, I have a solution!
This time I used a Lambda function to add 1 to the previous index (stored in base n) but before returning the value, I scanned the digits and copied the larger digit across.
WorksheetFormula = LET( count, COUNTA(Items), initialise, REPT("0",pick), indices, VSTACK( initialise, SCAN(initialise, SEQUENCE(combinations-1), NextCombinationλ(count,pick)) ), INDEX(Items, 1 + Explodeλ(indices)) )where
NextCombinationλ = LAMBDA(n,p, LAMBDA(initial,s, LET( place, n^SEQUENCE(1,p,p-1,-1), increment, 1 + SUM(place * Explodeλ(initial)), converted, BASE(increment,n,p), vector, Explodeλ(converted), modified, SCAN( , vector, Maxλ), CONCAT(modified) ) ) );supported by
Explodeλ = LAMBDA(term, MID(term, SEQUENCE(1,pick), 1)); Maxλ = LAMBDA(u, v, Max(u,v));I have just performed the calculation for 8 picks taken for 9 items in 300ms. Ther are 12,870 combinations and the permutations would have been over 43M!
It has just occurred to me that I stopped my 'Lambda-isation' one step short of a 'perfect' solution.
The principle (which might be better discussed on JKPieterse 's Lambda discussion LI site) is
"Every spreadsheet formula should be expressed as a Lambda function"!
The name chosen for the function should provide a succinct statement of what the function does, and the parameters provide an inclusive list of the formula's immediate precedents.
There are assumptions implicit in this statement. For example, it assumes that the user needs a clear idea of what the formula does but has no need to understand how it does it (the developer and auditor's needs are somewhat different and in the world of spreadsheets the user often adopts multiple roles).
WorksheetFormula
= CombinationsAλ(Items, pick)
CombinationsAλ
= LAMBDA(Items, p,
LET(
count, COUNTA(Items),
initialise, REPT("0",p),
counter, SEQUENCE(COMBINA(count,pick)-1),
indices, VSTACK(initialise,
SCAN(initialise, counter, NextCombinationλ(count,p))),
INDEX(Items, 1 + Explodeλ(indices))
)
);
Matt. I tried removing 'initialise', VSTACK and the use of initialise within SCAN but, this far, without achieving the perfect result!
Patrick. I have included a formula for your occurrence matrix, but the nested array problem makes it ugly. I should rework it as a Scanλ(…, Countifsλ) that uses REDUCE and HSTACK to emulate the intended SCAN while passing the COUNTIFS as a Lambda variable. I have done it before but none of this is a trivial exercise and it is annoying that such workarounds are needed.
Sorry
= MAKEARRAY(COUNTA(Items),pick,
LAMBDA(r,c,
COUNTIFS(INDEX(result#,,c), INDEX(Items,r))
)
)would have been better in this case.