Forum Discussion

ScottN1295's avatar
ScottN1295
Copper Contributor
Jul 12, 2024

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...
Excel
djclements's avatar
djclements
Bronze Contributor

ScottN1295 If you're working with a modern version of Excel (e.g. Excel 2021 or MS365), the following single-cell array formula should give you the desired results in a spilled range:

 

=LET(
    ye, C2:G2,
    ys, EDATE(--ye, -12),
    ce, I12:I14,
    cs, I32:I34,
    d,  IF(ye < ce, ye, ce) - IF(ys > cs, ys, cs),
    IF(d > 0, d, 0) / (ce - cs) * B32:B34
)

 

Results

 

Note: this assumes range C2:G2 contains the acutal Year End dates (e.g. 12/31/2024) with custom cell formatting applied (yyyy).

 

For older versions of Excel, use the following formula in cell C32, then copy it down and across:

 

=MAX(MIN(C$2,$I12)-MAX(EDATE(C$2,-12),$I32),0)/($I12-$I32)*$B32

 

These formulas were written to replicate the results shown in your original screenshot. However, the logic for calculating the duration in days between the start date and completion date (as well as between the year start and year end date) is off by 1 day. The completion dates should be the actual date the project was completed (e.g. 08/31/2025 instead of 09/01/2025). The formulas can then be written to add 1 to the duration calculations accordingly and return an accurate distribution amount for each period.

 

Please see the attached workbooks, if needed (v2 demonstrates the alternative duration calculation method mentioned above)...

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 14, 2024

djclements 

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.

  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 14, 2024

    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

Resources