Adding a value to a column, depending on the existing sum of the column

[badvoc0]

Hi all, first post.

I am trying to create a simple bookkeeping spreadsheet to separately track several budgets in one bank account, fuel,insurance,phone etc.

When i put money in the bank i want it to automatically divide into various budgets/sub accts using a percentage of the amount paid in, this all works fine.

What i now want to do is modify the formula for the percentage allocated to each budget so that it looks at the existing total for that budget and only puts money into that budget if it is currently below a predetermined threshold, for example if the budget for ‘phone’ already has two months worth of payments in it then dont put any more into this budget and put it into ‘general’ instead. Not surprisingly I am getting a circular reference error.

eg C3:C10 are to contain amounts paid in
and C11 is the total =SUM(C3:C10)

my formula in C3 is =IF(C11<‘phone reserve’;B3*10%;"") where B3 is the gross sum paid into the bank.

Does anyone have any ideas how i can do this without tying myself in knots, my knowledge level is fairly basic,

Thanks all

[joshua4]

Unless you want to get into seeing your account as a database table and tracking dailies, I’m wondering if you might not be better off just thinking of the account as a whole and divvying up the account into reserves from the current account total. Logically, since you don’t pay bills on one certain day, this means that the budgeting would really only be “correct” for one designated date within the monthly cycle, and you would just have to stick to that date. Of course, I don’t think it would be really accurate in any case, but it might give you an idea.

I am not an accountant. I’ve attached a quick spreadsheet that might be along the lines of what you are looking for, or that might act as a starting point for discussion for the technical questions. It is not meant to answer any accounting or financial questions.

BudgetingIdeas.ods (12.4 KB)

[badvoc0]

Hi Joshua4,
Thanks for taking the time to reply.

The essential problem ive got is that this account is for a very small business and there are several deposits made during the month which are then divided amongst a variety of reserves. I was looking for a way that deposits would be split amongst the various reserves, but if there was already sufficient in the reserve (eg 2 months worth) then no more money would be put into that reserve until it had fallen back below its threshold.

I think with the sample sheet you provided i can adapt mine to work something like i want it to.

Many thanks