Check Book Formula

[Brenda]

I'm a real green horn when it comes to excel, I've
searched the data base and am pretty baffled by some of
the questions...Wow....anyways, I feel kind of silly
asking as this is probably very simple. I just don't know
how i would go about setting up a check book style
spreadsheet. The last three colums would be Income,
Expense, Balance. I understand how to add or subtract,
what I don't know is how to I tell the spreadsheet to
SUBTRACT the expense to get the Balance or and ADD the
income to get the balance in each row. Do I have to type
a formula in each row to do so, or is there a simpler way.
Thanks.
Brenda

 

[Norman Harker]

Hi Brenda!

The balance formula is

=RefOfCellAbove+IncomeInSameRow-ExpensesInSameRow

So if my Last Balance was in D3 with Income in B4 and Expenses in C4
the D4 formula is:

=D3+B4-C4

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Andy Brown]

Hi Norm -- since Brenda said "I'm a real green horn" and "do I have to type
a formula in each row?", I'll add a little.

With your formula entered and its cell selected, hit the Copy button on the
Standard toolbar. Select the range of cells you want to copy it to using
left-click & drag, then hit Paste.

A possibly quicker way is to hover over the bottom right corner of the
formula cell until the cursor changes to a solid black cross. Then you can
left-click & drag the formula down to replicate.

Slightly OT Norm, a possibly aesthetic point that I've never seen covered ;
what is best practice here? Obviously a prog like MSMoney can analyse
because all items are (usually) pre-defined as income or expenditure (so
there's no need to state that the amount you spent on XXXX this month was
actually -$x, not +$x). So for this purpose in Excel, should you enter all
expenditure items as MINUS$x?

(NB - this would make the running total formula =D3+B4+C4).

Rgds,
Andy

 

[Norman Harker]

Hi Andy!

Thanks for additional help.

My preference with Excel is to follow a signage of money flows viewed
from the perspective of the "owner" of those flows. So in general
terms I'd sign expenses negative and add to get a running balance.
When dealing with quite large schedules and interpretation of
balances, I find it easier.

But it's important to note that this can be more difficult for
beginners and that a change in "owner" from (e.g.) borrower to lender
will reverse signs. Also third party payments will appear in (say) a
borrower's view but no in (say) a lender's view.

The methods of mastering signing of flows are not that difficult to
master. Away from the "owner" = neg. Towards the "owner" = positive.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[David McRitchie]

A similar question was asked today in another newsgroup as well.
With all the anonymous stuff [BS] I'm not even going to bother trying
to see if it was the same person. But for the purposes of
inserting rows and deleting rows the formula would better be
written as:
D4: =OFFSET(D4,-1,0) +B4-C4
instead of
D4: =D3+B4-C4

More information can be found in
See example in
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#insrtrow

Norman also answered later question for you in this thread.
Banks in the US anyway generally give you a check register
that is named as if you are keeping records for bank.

The Debit belongs on the left (money in), the Credit (money out), and then
the Balance -- that is if you want to keep records for yourself
and not for the bank. And that is the way that Norman showed.

 

[Guest]

Thanks to both of you for your help. I'm new to this kind
of thing and am pretty impressed with the help, and the
fact that one of the responses came from Australia!.(I'm
from small town in Manitoba, Canada its freezing cold
here!)..pretty neat. Thanks again.