Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running total for balance sheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running total for balance sheet
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 "DebbieV" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running total for balance sheet
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) "DebbieV" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I COPY A TOTAL FIGURE FROM 1 SHEET TO NEXT ADDING RUNNING T | Excel Worksheet Functions | |||
total running balance | New Users to Excel | |||
Having trouble finding a BASIC running balance sheet. in/out bala | Excel Discussion (Misc queries) | |||
How do I create a running total (balance) in a pivot table? | Excel Worksheet Functions | |||
rent received/balance owed/running balance spreadsheet | Excel Discussion (Misc queries) |