SergeiBaklan At the moment, the number of lambda functions I really need to take forward to new workbooks is probably pretty limited and could even be done as a template. Perhaps Accumulateλ() which applies a percentage change and a payment to an initial balance and calculates the array of opening or closing balances. Built on that is Allocateλ() which which links every output from a FIFO (first in, first out) problem to the input that feeds it. The following applies the function to calculate the percentage of iron in a delivered batch of ore
Calculating a moving weighted average during Resource Scheduling | Page 3 | Chandoo.org Excel Forums - Become Awesome in Excel
Also useful would be
ArrayRefλ
= LAMBDA(A, nᵣ, dᵣ, sᵣ,
LAMBDA([iᵣ],
IF(
ISOMITTED(iᵣ),
nᵣ,
LET(
r_, 1 + SUM(IF(dᵣ = "down", (iᵣ - 1) * sᵣ, 0)),
c_, 1 + SUM(IF(dᵣ = "across", (iᵣ - 1) * sᵣ, 0)),
INDEX(A, r_, c_)
)
)
)
);
which takes an arbitrary multidimensional array, described by steering data, and returns an element of the array as requested (I have only used the function for 3 and 4-dimensional arrays but it is not limited to that).
One thing that does trouble me is that the functions that tend to appear within the context of the AFE look so different from the formulas one would find by inspecting the content of a cell; are they not the same thing? An example might be the Microsoft Research Gist referenced by lori_m in a recent post. The feel of that was full-on specialist programmer territory.
Something I may try is to develop a workbook subject to the rule that all formulas must be placed within lambda functions. After all, I already treat direct cell references as errors (except, of course, in Name Manger where they are allowed in order to link sheet real-estate to business objects) and I avoid the practice of using relative references. Why not inflict a little more pain? Who knows, the outcome could be a coherent development strategy (or an unworkable mess?).