Entering Formulas

[Justme]

I am new to adding formulas to excel. I have created a money record (as a
check register book) and I am trying to figure out how to add a formula that
will allow me to input the debit and credits and then generate the correct
balance. Can anyone help me?

 

[Pete_UK]

You have to decide if you are going to use one column for the amount
of the transaction (in which case you will need another column to
indicate whether it is a debit or a credit), or if you are going to
use one column for debit amounts and a different column for credit
amounts. Presumably you will also have a column for the date, and you
might want a column for description. You will have another column for
the cumulative total (balance) and it is here where you will put your
formula, allowing the first entry to be the opening balance. You will
probably want to use the first row for headings, so that you know what
each column is to be used for (eg Date, Description, Credit Amount,
Debit Amount, Balance). If you set it up like this, then put your
opening balance in E2, and in E3 you can use this formula:

=E2+C3-D3

and copy this down column E as far as you need to.

Hope this helps.

Pete

 

[Sandy Mann]

With Dates in Column A, Reason in Column B, Credit in Column C, Debit in
Column D and the Balnace in Column E:

With the above column labels in Row 1, enter this Formula in E2:

=IF(COUNTA(A22)=3,C2-D2,"")

and in E3:

=IF(COUNTA(A33)=3,E2+C3-D3,"")

and drag down on the fill handle as far as you require.

This will calculate when you enter either a Credit ot a Debit but will
remain blank if you enter both in the same line.

Post back if you need further assitance.

--
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

 

[Justme]

Thank you so much. The formula worked. This is going to make my job a lot
easier. I have one more question for: When I copied the formula so that it
can repeat in each box after the first, but the total is in all the boxes
that I chose. When I tried to delete the numbers, the formula itself
deleted. How can I use the same formula without having the running total
shown?

Also, I heard that you can set up your worksheets to read and add
information from one to the other. How do I do this? I have one workbook
with 5 worksheets. How do I compute certain transactions on one worksheet
and it is computed into the other worksheet?

 

[Pete_UK]

You can amend the formula in E3 to this:

=IF(AND(C3="",D3=""),"",E2+C3-D3)

and then copy this down. It will not show a value until either C3 or
D3 (or both) have a value in them.

Hope this helps.

Pete

 

[Justme]

Thank you so very much. You are a wonder worker! It worked!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete