Question about balancing a check book using excel?

Question

Okay, I am trying to use excel to balance my checkbook. In columns D:D I have my payment amount, in column G:G I have my deposit amount, and in column H:H I have my balance amount. Now, what I am trying to do is, to come up with a formula that allows me to not have to add in my deposit amount to my balance amount each time. I would rather just to type in my deposit amount and my balance amount automatically changes. Right now each time I have a deposit amount I am using this formula for example =SUM (H21, G22, -D22), but I want for every time I enter my deposit amount my balance amount to automatically to change. I don't want to each time enter a formula to compute my balance amount if I have a deposit. I think I might use the SumIF function, but if so I don’t know what I put in the boxes. CAN ANYONE HELP ME PLEASE!!

Answer 1

Just sum them.

The paments should already be negative numbers.

Take out that - sign by D22, you can't put a minus sign there.

You can sum a range, like sum(D22:D50)

Answer 2

ROW 1:
>Column A - Date
>Column B - Check #
>Column C - Transaction Description
>Column D - Check Amount
>Column E - Deposit Amount
>Column F - Balance

All check amounts should be recorded as a negative number (-100)
All deposit amounts should be recorded as a positive number (100)

2F - Beginning Balance (whatever the actual dollar amount is)
3F - Only formula that ever needs to be written. It is:
>>>> SUM(D3:E3)+F2
Now just drag that formula straight down and you will always have an accurate balance.
-

Answer 3

This may be a different approach, but have you considered online banking?

I'm with Bank of America and I can log in and see where and when I used my debit or credit card within minutes of the transaction. Within 24 hours I can see the checks that were cashed and even see a picture of the front and back of the actual check.

Using Excel to balance your check book is rather like buying an automobile and using a horse to pull it. You are using a computer to track your finances, which is really just a high tech version of pen and paper. You could completely throw out that middle step and use online banking. This in turn would cut down on the need to even write checks, since you can just pay your bills online too.

Using online banking I find that I personally write less than one check per month. I can completely track my finances in real time. Bank of America does an outstanding job, I have not had a mistake of even a penny on my account since I signed up a couple of years ago. My wife and I have a lot of transactions too, mortgage, car payments, all the usual family stuff. But still, online is just so simple.

Answer 4

Sounds like you're having to do a lot of work... and computers are supposed to make life easier, aren't they? I have to recommend just doing it the old-fashioned way... paper and pen... but hey, use a calculator... at least it's easier.