Inserting Rows and Maintaining Correct Balances

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new to Excel and am still learning. I am working in Excel 2000. What I've
done is make up a spreadsheet for my checking account which, is working quite
fine except for a little problem that I have been experiencing.

Sometimes I post payments and deposits in advance. For example I may make a
posting for a November payment and am still in the month of October. Now that
I have entered the Nov payment and, then want to add another row to put in an
October payment, I find that the balance is incorrect and, the only way to
try and correct it is to copy the previous balance and paste it. However, my
continuing balance remains incorrect.

In short, what I am trying to do is to insert rows before payments that have
already been entered and, at the same time maintain the correct balance.

Will appreciate any suggestions.

Thanks
 
It is difficult to give an answer without more detailed info on how your
sheet is laid out but assuming that:

Column A: Dates
Column B: Name/Transaction
Column C: Deposits
Column D: Payments out
Column E: Balance

Then with the starting balance in E2, enter in E3:

=IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Enter this formula in Column A below your payments or a cell like C1

=SUM(A1:INDEX(A:A,ROW()-1))

If you insert a row in column A and add a value, the formula will adjust to
include that new value.


Gord Dibben MS Excel MVP
 
Sent too soon:
Then with the starting balance in E2, enter in E3:

=IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2))
Click to expand...

and copy down on the fill handle as far as required, (which can be futher
than your existing data).

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy Mann said:
It is difficult to give an answer without more detailed info on how your
sheet is laid out but assuming that:

Column A: Dates
Column B: Name/Transaction
Column C: Deposits
Column D: Payments out
Column E: Balance

Then with the starting balance in E2, enter in E3:

=IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Having seen Gord's post I realise that I was misunderstanding what you were
saying about inserting a row, my formula was designed to accept entries but
not add the amount in until the payment date.

Better to go with Gord's answer.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy Mann said:
Sent too soon:
Then with the starting balance in E2, enter in E3:

=IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2))
Click to expand...

and copy down on the fill handle as far as required, (which can be futher
than your existing data).

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Back
Top