How to divide a cell's value evenly across a range of cells in Google Sheets?

Question

I have a Google Sheet with values based on each month of the year:

https://docs.google.com/spreadsheets/d/1XIm-1eZZBVttvRECUlU8tCWjVFU8rbMrpypRbn88j00/edit?usp=sharing

There's a Target, an Actual and a Difference column:

I would like to add a formula that would automatically and dynamically adjust the Target column values (B3 to B14) based on an equal division of the value of the Total Difference (D2) divided by the number of months left in the year.

To put that in context; in the screenshot above there is -£600 difference. Let's say we are in January currently. I'd like to divide that £600 equally among the remaining months of the year (i.e. Feb - Dec, so 11 months) and then adjust the Target amount for each month by that sum.

In practice, this would mean increasing the target for each month of Feb - Dec (cells B4 to B14) to be £54.55 higher (i.e. -£600 / 11 = -£54.545, round it to the nearest penny, -£54.55, then make it a positive value; £54.55)

The overall goal is to dynamically adjust the targets as the year progresses, depending on how much difference there is in the total amount.

So, naturally, as the year progresses, the B column values that would be adjusted would become less and less cells (i.e. in Jan, it's 11 cells - Feb-Dec - whereas if we were in June, for example, there would only be 6 cells (B9-B14) to divide the Total Difference figure by).

How can I do this?

Answer 1

I don't know if I full understand your purpose, but if you want to evenly distribute the difference from your target value (12.000) between the remaining months of the year; then you can set your target in B2, and change D2 to =C2-B2:

Then, in B3 you can set this formula:

=MAP(SEQUENCE(12,1,0,1),{0;C3:C13},LAMBDA(mon,amount,
IF(mon=0,B2/12,(B2-SUM(C3:INDEX(C3:C13,mon)))/(12-mon))))

Then, you'll have this chart: