Running total for balance sheet

[DebbieV]

Hi

I was wondering if anyone can give me a formula for a spreadsheet that
I am maintaining a list of transactions and has an amount due column.

I want to have a seperate cell that will always have the last amount
that appears in the amount due column.

Is there a formula to identify the last entry in the column if I keep
adding entries in the rows.

cheers
Debbie

 

[FSt1]

hi
try this...assuming your amount column is A..change to suit.
=OFFSET(A2,COUNTIF(A2:A500,">0")-1,0)
it uses the offset function to return a value offset in row and columns from
a point.
in this case point is a2, rows offset is what ever is in the column and
colunms offset is 0.
the countif function counts the rows for the offset function.

regards
FSt1

 

[Chip Pearson]

If your amount due values are in column C starting in C10, you can use the
following array formula:

=OFFSET(C10,MAX((C10:C100<>"")*(ROW(C10:C100)-ROW(C10))),0,1,1)

Change C10 to the first cell of the amount due column and change C10:C100 to
the largest range that may contain amount due values. The formula returns
the last (in the greatest row number) non-blank value in C10:C100.

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces. The formula will not work properly if you do not
use CTRL SHIFT ENTER to enter it. See
http://www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about Array Formulas.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)