Forum Discussion
How to evenly distribute an amount across multiple periods that are variables
I have a total dollar amount that needs to be spread over multiple periods. The periods are tied to the construction of a facility. As such, the timeline needs to be easily adjusted in case the const...
Some interesting considerations. By avoiding MAX/MIN and using broadcasting inequalities you managed to avoid the array of array challenge. It should be the fastest solution. My solution assigned amounts by the month (rather than day) but I am not sure what the OP intends.
PeterBartholomew1 Yes, I came to the realization recently that IF can be used with comparison operators when working with arrays, instead of having to rely on MAP with MIN/MAX.
However, if the situation calls for it (if the calculation is unable to spill naturally), you could resize the arrays first, then use MAP to loop through them together (escaping the array of arrays problem). For example, in reference to the question at hand:
=LET(
RESIZE, LAMBDA(n, CHOOSE(n, C2:G2, DATE(YEAR(C2:G2), 1, 1), I12:I14, I32:I34, B32:B34)),
MAP(RESIZE({1}), RESIZE({2}), RESIZE({3}), RESIZE({4}), RESIZE({5}),
LAMBDA(ye,ys,ce,cs,amt,
MAX(MIN(ye, ce) - MAX(ys, cs) + 1, 0) / (ce - cs + 1) * amt
)
)
)
RESIZE and MAP multiple arrays