Distribution of Values Across Cells

[rinnytintinnyrms]

Running Professional Pro 2013.

I'm looking for a Formula that will distribute values across multiple cells for budgeting. I want them to change based on what is input into previous cells while still adding up to the same number. I might not be explaining this well which is why I'm struggling to find the right formula.

For example:
My Budget for one row is $12,000
I want that $12,000 to start off split evenly among 12 Months, so $1,000/month.
Let's say I spent $1,500 one month, that would effect the future months, decreasing their value, or, if I spent less, increasing their value.
The entire sum of the row should maintain $12,000 however.

I hope this makes sense. I'm still new to Excel's functions so I'm sure there's a very simple answer to this.

 

[Eric W]

Welcome to the forum!

Here's one option:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Budget	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]12000[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=B2/12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=($B2-SUM($C2:C2))/(12-COLUMN(D2)+COLUMN($C2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O2[/TH]
[TD="align: left"]=SUM(C2:N2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Create a spreadsheet as shown above. Enter the budget in B2. Enter the formula in C2. Enter the formula in D2, then copy it to E2 to N2. The formula in O2 is optional, and only so you can see that everything sums up properly.

Then you can copy the C2:O2 formulas down the sheet as far as you'd like.

Now, once you've completed January, and you have the actual amount to put in, put that amount ($1500 in your example) in C2. That will overwrite the formula in C2, which is what we want. The other formulas in the row will all adapt automatically.

Let me know how that works.