Calculating bank accounts

Calculating bank accounts
To give a specific example of what I want:

Column A is check #, Column B is date, Column C is payee/source; Column D is amount of check; Column E is amount of deposit; Column F is balance.

So I want to be able to indicate that an amount in column D should be subtracted from Column F upon entry, while an entry in Column E should be added to Column F when entered, and new balance entered automatically in Column F?

How can I get the computer to perform these calculations automatically as I make the entries?

F-D=F1
F1-D1=F2
F2+E=F3......=Fn

Is this possible without advanced programming experience???

Of course it's possible, but you need a spreadsheeting program (if you want to use Works, you'll have to use the spreadsheet portion of it, and the formula will be different. Someone else will have to help you with that.).

Here is how you do it using Excel. As you said, Column A is check #, Column B is date, Column C is payee/source; Column D is amount of check; Column E is amount of deposit; Column F is balance.

1. In the first row, put in the date (no check number!) in cel B, the words Opening Balance in cel C, don't put anything in cels D or E, and in cel F, type in the amount of the initial balance.
   
2. Now, in row 2, in the f cel of the row, type this: =F1-D2+E2
   
3. Move your cursor to the lower right corner of cel f2 until the cursor becomes a black cross. Holding down the left mouse button, drag down for as many rows as you want.
   
4. Then just fill in whatever you want to in cels A - E in those rows. If you need more rows, just repeat step #3.

I do NOT recommend buying Excel just to make an electronic version of a check register. If you already have it, I would use it for this (we do), but if you already have Works, use that.

Calculating
Jespah! Wonderful! Works handled it perfectly! I cannot thank you enough.

Calculating bank accounts
Oh the horror! I had two months worth of register entries all neat and tidy, and was so impressed with myself! Then when I went to extend the final balance column (actually the G column in this case) I deleted the whole thing, and now when I try to start over with the formula all I get in the =G1-E2+F3 box is a zero!

Also, balances have begun to appear with parentheses around them showing negativity! WHAT have I done???

uh oh....tomkitten, here's a VERY important icon for you to know and become friends with...

Meet, the UNDO command.



Whenever you're working along and you mess something up, just click the undo icon and it will take you back one step. Sometimes you'll need to click it a few times to get back to where you recognize what you were doing.

Also, if you're working on a project, and the you're not sure what's going to happen when you make your next move, click the little "save" icon (looks like a disk), that way, if you mess up you can undo or close you file and reopen it back up.

You hadn't mentioned the G column before, what where you going to do with that one?

Anyway, if you wanted to move your moving balance total over to the G column (it seems that's what you want) highlight all the cells in Column F by clicking on the first cell in F, and draging your mouse down until all your cells are highlighted, then, release your mouse, go to the bottem highlighed cell, find that little bold cross again, and drag the formula over. That will make all the formulas in F copy to G.

Unfortunately, you'll have to re-enter your data, just click the save icon once in a while to save your work.

One other thing, when you get everything the way you want it, you'll want to protect your worksheet so you can type just where you need to enter the info in columns A through E. That way, you won't accidently delete a formula and have a heart attack.

Here's what you do...

place your cursor ON the column header that says A, not the cell below the A, ON the A. The entire A column will highlight.
Now drag your mouse over B C D E and those columns will highlight also.

Now, you need to do 2 more things, while these colums are highlighted.

1. click on Format, click on Cells....In the window that appears, click on the little tab that says Protection. See the little check mark next to the word Locked? Click on that to get rid of the checkmark. Click OK

2. Now, click on Tools, go down to where it says Protection, you'll get a submenu where a choice is Protect Sheet, click on that.
A window will appear that asks you to pick a password. You can do that if you want, or you can just click OK.

Now, you will only be able to type in the cells you "unlocked"

To unlock the sheet, like if you want to change some formulas, just click on Tools, go down to Protection, and choose, Unprotect sheet.

You might also want to unprotect it to make columns various colors, or widen columns or something.

OK - let's assume you are just going to keep everything on this one sheet, running on "forever" You'll notice once you get a few rows filled, you can no longer see your headers. Do the following to always be able to see your headers....

Let's say you always just want to see what is typed in rows 1 and 2, and columns A through G....
Click on cell H3....that's in column H, the 3rd cell down, in row 3.

Click on the word "Window" on the tool Bar...it's probably next to Help.
Move your cursor down to the word that says "Freeze Pane" and choose that.

Now, you'll see as you scroll down, you can always see what's in rows 1 and 2. You get rid of Freeze Pane by clicking on Window, Unfreeze pane.

OK, back on task, the reason you got negative numbers is, well, because your formula is telling it to do that.

The thing you tped in that one cell? The =G1-E2+F2? What that is saying in English is....take the number in the cell G1 and subtract the number in cell E2, then add in the number in cell F2. The = sign in the beginning is just the way you tell excel that you are telling it to perform a calculation.

Think of it as a backward equation. Instead of saying

50-25+75=100 excel needs to hear is as

100=50-25+75......you type in the =50-25+75 and it tells you 100.

Are you following?

If you aren't just ask. Jespah and us love this stuff, don't we?

Calculating bank accounts
Well, unfortunately, the undo didn't carry me back far enough, so what's gone is gone. That's a minor nuisance, though.

What really bothers me is why can't I duplicate the formula entered just as Jespah gave it to me? It's very straightforward, and when I followed Jespah's instructions all the entries in the running total (the right-hand or G column) tidied themselves up and two months of balance calculations arranged themselves to perfection, without my interference.

I really prefer not having to calculate the balance by hand, so to speak, every time I have a check or a deposit, going back and forth between the computer and the calculator. It was so nice to have it done automatically...

Next time, when you've messed up, close the spreadsheet and don't save changes. That will undo anything and everything you've done, so long as you haven't saved since the screw-up.

Why are you using the G column? I thought the F column was for the actual balance. Your final balance is your actual balance, isn't it? So maybe I'm just confused.

And yes, Chai and I love this stuff.

I dont know whether this will help but......





When you dont get the answer you expect try Audit formula.

There are tutorials available about audit formula but I found "playing" was the best way to learn.

Re: Calculating bank accounts


Yeah, I don't think either of us understand what the G column is all about.
I thought the F column was for your running totals?

Anyway....as far as duplicating the formula exactly...

When you drag a formula down or to the right, excel automatically assumes you want to reformat everthing to its new perspective.

That is, dragging a formula down would turn =F1-D2+E2 into
=F2-D3+E3
=F3-D4+E4
=F4-D5+E5
=F5-D6+E6.....etc.
see how it's adding another row # on?

If you drag that =F1-D2+E2 to the Right, you would get....
=G1-E2+F2......=H1-F2+G2......=I1-G2+H2.....etc.
see how it's moving the formula to the next column, leaving the row # alone?

IF what you are asking is how to move the formula to Column G, but have it be the exact same formula as in Column F...I'd suggest you just type the formula in by hand, making it the formula you want, then dragging it down column G.

Or, here's another way....

In column F, put a $ sign in front of each letter in the formula, making it
=$F1-$D2+$E2

The $ sign is telling excel to make those column #'s constant, do not change them no matter what else you do.

NOW, you can redrag the formula down, so each row has the new formula with the $ sign....THEN, highlighting the cells going down, drag the formula to the right.....then the exact same formula will appear in column G.

So, we're both dying to know...what are you using column G for?

tomkitten, could you please tell us what you are using each column for now....The heading on each I mean?

That would help.

Calculating bank accounts
I was using the G column out of laziness, I guess. The C column, for payee/source was too small; I should have stretched it, but instead I was using C& D for that purpose. However, it worked perfectly well using the G column.

No, I'm not trying to move anything, simply to have the running total appear in G, but if I stretch C, then F becomes the Balance column, as in your examples. Does the program distinguish between F & G for calculation purposes? Does it matter what letter heads the column?

Anyway, when I tried it again with completely new figures, it worked. I have another approach which may save me the trouble of starting AGAIN from scratch - i.e. can I begin using the formula after 20 or 30 rows of not using it?

BTW, I'm not using Excel, but Works.

Yeah, widen the column that has the writing in it, don't use another column.

Yes, you can start using the formula again, anytime you want...

however, if you want to use F as your total column, just go to the last cell with the formula, find the dark cross bars, and drag the formula down as far as you want. You can drag it down hundreds of rows it you want.


OR - if you WANT to not have the formula for some reason for a few rows, and start again....do this...

Right click on the cell with the formula and click on "copy" You'll see that cell will get a little blinking border around it.

Go down a few rows to the cell you want to start calculating in again, right click, and this time click "paste"

It will paste the formula there, adjusting the numbers for its new position. When you start typing again, the blinking border will disappear, or you can hit the Esc key (upper left on keyboard)

That's why I encourage you to unlock the cells you'll want to type in, and protect the sheet. It's just too darn easy to accidently delete a formula.

Also, take a couple minutes to figure out what the formulas are saying to do. For instance, when you copy and paste the formula above, observe how the formula changed, but is still performing the same function.

You can drag formulas as far down or to the right as you need them.

Does this make sense to you?

If you're unsure re widening the column, you can make the text wrap if you're using Excel.

To wrap text in the C column.

1. Click the top of the column, on the C. This will select the entire column.
   
2. Under the Format heading (all the way at the top), select Cells
   
3. You'll see a few tabs on the little menu that pops up. Select Alignment.
   
4. Under Text Control (it's on the left on that little menu), click inside the check box next to wrap text
   
5. Click Ok

Calculating bank accounts
Jespah - I've ben trying like crazy to follow the instructions for this as given in Works 6 for Dummies and get absolutely nowhere!. But your Excel instructions work perfectly. Weird!

Did you ever get that formula thing straightened out ?

Tom Kitten, Always have a backup of whatever worksheet you prepare. It'll save you anxiety and hours of rework. Depending on activity, you should back it up every 5 to 10 entries - or periodically according to your calendar (e.g., once a week, twice a month, or monthly).

Calculating bank accounts
Cicerone - I'm pretty good about backing up, and now I have the CD burning capability, I hope to be even better. However, virtue is not always triumphant - Last winter I faithfully put everything of any importance at all onto a movable hard drive.

And the hard drive died.

I'm well aware that shet happens even when we take some precautions when it comes to our computers.

I used to think my thousands of pictures were safe, because I had antivirus software, but our son tried to load some software that crashed my computer. Something I hadn't expected. I had a local guy save some of the pictures from the harddrive, but almost all my worksheets and other stuff were gone because they were on Lotus worksheets.

I now keep backups on my other computer, and make disks almost monthly. I always print out hard copies almost every week.

TomK, if you're still not happy with your sheet or want another one then email/PM me and I'll send you a sheet that's already set up for month to month accounting.
Running totals, month by month totals, stuff in, stuff out etc... etc...
It's dead easy to use. Just stuff your numbers in and that's it.

Calculating bank accounts
Chai tea - yes, it's working fine now. But I still want Heliotrope's accounts sheet; perhaps I'll do a more detailed record of in and out over the months - the New Year is a good time to start.