Need help creating a formula that automatically reallocates unspent funds
 

So lets say I have $10,000 to spend in travel from Jul to Jun. For budgeting purposes I have spread this evenly throughout the whole year ($833.33 per month). However, I am not spending it this consistently. In fact, I haven't spent any of it for the first three months of the year. I want to spend it all or I will lose it.

My spreadsheet is dynamic so when I update the month it looks for values in a database and reflects actual costs. Since I did not have any travel expenses in the first three months it assumes that money will not be spent and the new projected spend is $7,500 because the first three months are now empty. I need a formula that will take the $10,000 and evenly allocate it across the remaining 9 months, so each month would show a budget of $1,111.11 now.

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:
(Original budgeted amount ($10,000) - money spent through current month($0)) / remaining months in the year(9 months).

My current formula after the Vlookup to pull from the database is as follows: IF(Firstmonth (greater than) currentmonth, 10,000/12,0)

Need help creating a formula that automatically reallocatesunspent funds
 

Hope this can help. (attachment)

Best regards
DM

12.10.2017. u 17:43, Wangs930 je napisao/la:
So lets say I have $10,000 to spend in travel from Jul to Jun. For
budgeting purposes I have spread this evenly throughout the whole year
($833.33 per month). However, I am not spending it this consistently. In
fact, I haven't spent any of it for the first three months of the year.
I want to spend it all or I will lose it.

My spreadsheet is dynamic so when I update the month it looks for values
in a database and reflects actual costs. Since I did not have any travel
expenses in the first three months it assumes that money will not be
spent and the new projected spend is $7,500 because the first three
months are now empty. I need a formula that will take the $10,000 and
evenly allocate it across the remaining 9 months, so each month would
show a budget of $1,111.11 now.

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:
(Original budgeted amount ($10,000) - money spent through current
month($0)) / remaining months in the year(9 months).

My current formula after the Vlookup to pull from the database is as
follows: IF(Firstmonthcurrentmonth, 10,000/12,0)

Why can't I find the attachment anywhere?

Need help creating a formula that automatically reallocatesunspent funds
 

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.

That was enough to get me rolling so I could adapt it to my sheet, thanks!!