I remember first discovering that a named formula could form part of a sequence that would be evaluated by Excel as a multiply nested formula but which could be listed to appear as a program segment. It took me a while to commit to this process and repackage intermediate array formulas, then appearing in helper ranges, as named formulas. It seemed almost sinful, flying in the face of all the 'tips and tricks' so earnestly published as the right way to use Excel. Despite that, it has worked just fine for me over a number of years.
Now I have a further choice. I can strip out all of the intermediate named formulas that I use as building blocks to create solutions and repackage them using LET. The question is 'how far should I go with this?' Is 10 lines (19 parameters) OK; what about 20 lines? When I have created a working solution, how easy would it be to find someone to maintain it?
As a further example, I draw from a discussion I created on Chandoo
https://chandoo.org/forum/threads/relative-referencing-is-wrong.39875/#post-261748 , Item#15
= LET(
dutyTable, SIGN(dataRange="x"),
m, ROWS(dutyTable),
n, COLUMNS(dutyTable),
k, SEQUENCE(m*n),
RNum, 1+QUOTIENT(k-1, n),
Cnum, 1+MOD( k-1, n ),
criterion, INDEX(dutyTable,RNum,Cnum),
combinations,
IF( order, INDEX( employee, RNum), INDEX( weekday, Cnum ) ),
dutyList, FILTER( combinations, criterion,"null"),
SORT( dutyList )
)
Order is a range containing {0,1} or {1,0} used to order the columns and so change to effect of SORT.
It is another unpivoting exercise, this time matching employee names against shifts by entering 'x' into a crosstab array. The output is a list, sorted by employee or by date of the shift.
Is this a good use of LET or is it simply taking the idea too far?