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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 13, 2024

ScottN1295 

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.

Resources