In my opinion, this formula is better aesthetically - but the date-based one would work, too.ĪutoFill: You'll want to get really well-acquainted with this Excel feature, as it will save you untold amounts of time and keypunching tedium.ĪutoFill can be used for a multitude of things, but perhaps its most-helpful use is to automatically fill (get it? "autofill") formulas into adjacent cells once you've created the first formula. The only thing that changes with this formula is that the BALANCE column will be blank until data is typed into either the DEBIT or CREDIT columns (Columns F and G, respectively). Or, in OpenOffice Calc, the formula would be: =IF(AND((ISBLANK(F7)) (ISBLANK(G7))) "" SUM(H6-F7+G7)) In that case, for Row 7, Column H, the formula would be like so: =IF(AND((ISBLANK(F7)),(ISBLANK(G7))),"",SUM(H6-F7+G7)) But what if we wanted to base our formula on whether or not our DEBIT and/or CREDIT columns are blank? Well, we could do that, too. Since every single transaction that we might enter into our Register should have a date, we can be pretty comfortable basing our "IF(ISBLANK)" formula on what's found in Column B. In row 6, let's make an entry for an ATM withdrawal of $20, and see what happens: Once you enter data there, the formula will do its thing. In plain words, the formula above tells Excel this: =If (B6 is blank), then show empty space otherwise, sum(h5-f6+g6))Ĭell B6, of course, is our "DATE" column.
You'll now have a "blank" Cell H6, yes, but the cell's not truly empty, as we see in our Formula Bar:
Excel debit credit running balance manuals#
Thus, in OpenOffice, the above formula should be changed to:įor more info on the difference between Excel and Calc, check out the OpenOffice manuals at: OpenOffice Users: One of the big differences between OpenOffice Calc and Excel is that Calc uses semicolons to separate parameters in functions, whereas Excel uses commas. Three transactions into our Register, and we haven't had to use a calculator even once.
Excel debit credit running balance plus#
Using our formula, that's the sum of: $165.81 (cell H3) minus $29.50 (cell F4) plus $0 (Cell G4).Īnd finally, for Cell H5, enter the following formula: =SUM(H4-F5+G5) Now you should have:Įxcel gives us a new balance of $136.31. And press to enter the formula and leave the cell. Learn more about the Excel's Formula Bar by clicking Excel's Help feature and searching for "formula bar." You may enter formulas directly into the cells themselves, or by placing your cursor in a cell (making it the so-called "active cell") and then clicking the Formula Bar and typing your formula there. Place your cursor in any cell, and if there's a formula in that cell, it'll show up in the Formula Bar. There's our formula!įormula Bar: Excel's Formula Bar typically resides just above the column headers. The cell shows us $165.81, of course, but look at the Formula Bar above the spreadsheet. Now let's move our cursor back into Cell H3. I can't think of a reason why you'd ever have both items in a row, but even if you did, the formula could handle it.Ĭell H3 now shows $165.81, which is $200 (our Opening Balance) minus $34.19 (our first debit, in Cell F3) plus $0 (our nonexistant credit in Cell G3). I created the formula this way so that it accounts for the possibility of either debits or credits in any row. In Cell H3, show us our Opening Balance (H2) minus our next debit (F3), if one exists, or plus our next credit (G3), if one exists. behind the scenes, in a way.īasically, by entering this formula, what we're telling Excel is this:
Once you've entered the formula above (exactly as shown!), press either your or key to leave Cell H3. Once there, type in this formula: =SUM(H2-F3+G3) (There's no way Excel could know our account's opening balance, right?) That cell is reserved for our account's opening balance, which the user must enter by hand. It's important to note that we don't need a formula in Cell H2.
Since Column H ("BALANCE") is where we need Excel to do the math for us, that column will be our focus. It's time now to learn a little about formulas, and build one that does what we need. The whole idea of this exercise is to make a spreadsheet so that Excel can help us keep tabs on our checking account balance. Any geek can tell you: Formulas are where Excel gets its number-crunching power.
0 kommentar(er)
