newbie question

[Steve]

Hi
My mate rents out his house and he'd like me to devise a simple spreadsheet
that would help him track his finances. He rents his place out to a mate and
his mate doesn't pay on a regular basis but a lump sum every so often.
What I thought is column A has the months in, Column B has the actual rent
in it (£300 pm)
Column C has the rent that his mate pays and Column D the arrears/credit

What formula could I use that calculates column B against Column C to show
the arrears/credit in Column D?

TIA
Steve

 

[CLR]

With D1 empty, put this in D2 and copy it down column D.........

=((D1+B2)-C2)

Vaya con Dios,
Chuck, CABGx3

 

[Steve]

With D1 empty, put this in D2 and copy it down column D.........

=((D1+B2)-C2)

Vaya con Dios,
Chuck, CABGx3

Thanks Chuck

 

[David McRitchie]

Hi Steve,
A little elaboration on Chuck's reply:

Better to modify that formula
D2: = D1+B2-C2
to one that can withstand insertion and deletion of rows
without messing up the balances or formulas.
D2: =OFFSET(D2,-1,0) + B2 - C2
note all cells directly addressed are on the same row.

Also none of the parentheses in the original formula were needed.

More information on OFFSET and insertion/deletion of lines.
Why must we use that funny looking OFFSET Worksheet Function
http://www.mvps.org/dmcritchie/excel/excel.htm#offset
Actually your question is very much like a checking book balance
so as usual when looking at a webpage look at the entire webpage.

Information on use of the fill handle to copy formulas down
http://www.mvps.org/dmcritchie/excel/fillhand.htm
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm