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 construction takes longer or shorter than expected. The amount to distribute can occur evenly over the time period. I white-knuckled a formula but was wondering if there is a more elegant way to calculate this.
In the below example, the cost to build a Commercial Scale Facility is $15,000. Construction will begin on 9/1/2024 and be completed on 9/1/2027. The cost is evenly distributed between each month of construction.
ScottN1295 I replicated the model from your picture and came up with the following formula, eliminating all the IF's and TRUE/FALSE checks. Though, the result may be slightly different for partial years, due to rounding.
The file is attached.
Riny_van_Eekelen thanks for the reply.
I noticed in the model you built the Yearly Periods at the top are as of 1/1/XXXX instead of 12/31/XXXX. When I used your formula with the correct period times the formula didn't work.
Note - the only change I made to your excel was the day of the Yearly Periods at the top to conform to 12/31/XXXX.
ScottN1295 But is essential for the formulas to work that you leave the dates in the header as the were. Otherwise the formulas have to be re-written with a different, though similar, logic.
This is a 365 dynamic array formula. It works in units of 1 month.
=LET( breakdownϑ, MAP(costs, construction, completion, LAMBDA(cost, construct, complete, LET( start, SORT(HSTACK(construct, year), , , TRUE), finish, SORT(HSTACK(complete, year), , , TRUE), months, DATEDIF(start, finish, "m"), splitϑ, LAMBDA(cost * months / SUM(months)), splitϑ ) ) ), DROP(VSTACK( INDEX(breakdownϑ, 1, 1)(), INDEX(breakdownϑ, 2, 1)(), INDEX(breakdownϑ, 3, 1)() ),, 1 ) )The calculation is the first part of the formula. Because Excel does not support an array of arrays as yet, the result is returned as an array of functions (thunks), breakdownϑ. There are a number of ways of expanding these with varying levels of efficiency which depends upon the number of rows and columns to be returned.
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)*$B32These 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)...
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
