Backstory: I built a budget spreadsheet for my personal finances. I calculated my obligations for the month, and the leftovers I want to divy up into specific savings account buckets on a monthly basis. Because my monthly expenses can be variable, I built a simple cell by cell formula method to make sure I’m saving proportionally to what I have available.
Below are the following columns:
Saving Buckets: the categories to save for every month
ideal/month: My ideal month’s savings. For example, I ideally want to save $200 per month for car repairs/maintenance. Ideally, I want to save up to $3,525 per month total.
ideal proportion: taking the ideal savings, making it a proportion of the total goal savings. For example, the car savings is 200/3525 = 0.057
Multiplier (1 or 0 ) : So available savings multiplied by ideal proportion multiplied by the multiplier will be the total I want to save in each category. If the multiplier is 0, the category will get no savings for the month. If I don’t want to save for vacations this month, I mark vacations as 0.
Month’s Savings: What I should save for each category based on what I actually have to save. For example, for the month of June, I had $2,987.94 to save, so although I ideally want to save $200 a month, I can only save $169.53 ($2,987.94 * ideal proportion * multiplier)
Now, this sounds very basic, but here’s where I run into problems. Let’s say, for this upcoming month, that I don’t want to save for gifts or car repairs, so I set those with a multiplier of 0. What I then want is all the other buckets to adjust to the reduction of categories. So if I set one or multiple categories to 0, I want those savings distributed to the other buckets proportionally. In other words, I want it to auto-recalculate as soon as I change the multiplier, so that I can know how much to save with the buckets I want to save.
My guess is to adjust the ideal proportion to be a sumif function, where if Multiplier is 1, then proportion normally. If 0, then add distribute that proportion to the other buckets, but I haven’t found a neat solution to this.
