Balancing

**reficuul** · 04-30-2010, 10:59 AM

So this one is pretty hard to explain, and I'll try my best... Searcher all over the internet and I couldn't find anything, not sure if I was searching the "correct terminology"

My wife and I are trying to set up Sub-Accounts within our Savings account using excel. The savings account will have X amount of dollars in it, and the excel sheet will have that X amount of dollars broken down into different Sub-categories (christmas, car repair, etc...) using a %. Each month we add X dollars, and those dollars get broken up, using a fixed % into each sub category.

I am sure I am already re-inventing the wheel, I just want to do this myself so I know its "right"

My problem is, how do I add a one-time adjustment into the sheet... that "disappears" once added in. I have it set-up so that if we take a withdrawal from the sub-account, that it will effect the total in that account... however, in my "Adjustment" category, the negative number still appears... I want the ability to add in the withdrawal (or one time deposit), have an effect on the total, and then disappear..

Can this be done??

Thanks,

~refi

**ChemistB** · 04-30-2010, 11:19 AM

It would help if you attached your worksheet with dummy data and explained which cells or columns you need changed or functioning differently

**reficuul** · 04-30-2010, 12:54 PM

here is a sample of the doc

**DP978** · 04-30-2010, 01:20 PM

What do you want to happen to the amount in SubAccount when that value goes away?

As soon as you remove 150 the -5 goes to 145...

Are you trying to keep the -5 as well?

**reficuul** · 04-30-2010, 01:55 PM

Originally Posted by DP978

What do you want to happen to the amount in SubAccount when that value goes away?

As soon as you remove 150 the -5 goes to 145...

Are you trying to keep the -5 as well?

i'd like to keep the negative 5 there, but the number under the adjustment to go away...

is there an option/format something that when you put the number into the cell, it calculates it / uses it, and then returns it as nothing?

**DP978** · 04-30-2010, 02:11 PM

Unfortunately, No.

You are going to need to use VBA to accomplish this.

**ChemistB** · 04-30-2010, 02:24 PM

Here is a VBA answer. Right click on the tab key > "View Code" and paste this code in. Adjust the range (C7:C12) as necessary

Please Login or Register  to view this content.

Does that work for you?
You can modify the formula's in Column B not to include Column C anymore. The code will do the math.

**reficuul** · 05-01-2010, 12:33 AM

it works, but it takes the formula out of cells b7 and b8 ... thus if I add a deposit under August, it doesn't effect either of those 2 cells.

**DonkeyOte** · 05-01-2010, 03:24 AM

You can't really do what you want to do I'm afraid with formulae alone.

If you want a formula to calculate the current balance (Col B) then it follows that you must have a reference to the one-off withdrawals somewhere in that function so as to maintain the correct closing balance.

Either

a) you must store cumulative withdrawals somewhere - and perhaps when you want to make a new withdrawal you type this into a cell and use VBA to generate the new cumulative

b) you modify the formula in B to hold the cumulative withdrawals as part of the balance (so not storing the current / cumulative withdrawals in a cell anywhere)

In terms of option b ... if you modified the formula in B to say:

B7: =SUM(D7:BB7)+N(0)
copied down

Then used the below code (replacing Chemist's prior suggestion)

Please Login or Register  to view this content.

You should find that as you enter one-off's into column C the balance in B is adjusted and the value in C is removed - the cumulative being held within the N function.