I need the formula to update every time I change the month and to
basically do this calculation and spread it evenly across the remaining
months
.. . . . .
Here is a test spreadsheet that shows my formula if it helps:
https://www.dropbox.com/s/qotn6licdg...heet.xlsx?dl=0
I'm hoping the following might help getting started.
Change the formula in cell F12 of Test_Sheet tab '12345678', by replacing "$E12/12" by the following fragment:
($E$12-SUM(OFFSET($E$13,0,1,1,DATEDIF($F$9,$F$8,"M")+1)))/
(11-DATEDIF($F$9,$F$8,"M"))
Then copy F12 across to Q12.
In this fraction, the numerator is the budget remaining (the original minus the historical months' consumption); the denominator is the number of months remaining.
Test_sheet has 4 months of historical data (rather than 3), leaving $9,500 to be spread across the 8 remaining months, giving $1,187.50 per month.