CODEWITHSUNDEEP
excelexcel-formula
REMESQ
REMESQ

Reputation: 1210

Running Total of Account - How to Display Last Number

I have the following formula in multiple rows to keep a running total on the last column:

=IF(ISERROR(IF(OR(C12,D12),((E11)+C12-D12),)),"",IF(OR(C12,D12),((E11)+C12-D12),))

My table is set up thusly:

C                    D                    E
 Charges              Credits              Account Balance

The first line under the column headers is just a "Running Balance", so whatever number is in there will be added (see the ((E11)+ part, and then subsequent rows will use Column E to allow for the running balance.

I never have a DEFINITIVE row where a balance for today would be for a particular client (e.g., Client 1 made 10 payments, so it's the 11th row, and Client 2 only made 2 payments, so it's on the 3rd row, etc.). I have one running balance file per client. However, I want to pull this data into another file that lists all my client's balances.

How can I insert a formula in that other file to give me the current running balance if it's not always in the same row?

Upvotes: 0

Views: 224

Answers (1)

amoy
amoy

Reputation: 181

The formula I use to track the running total of my books is this formula:

=SUM(OFFSET(C3,,,MATCH(9.99999999999999E+307,OFFSET(C3,,,ROWS(C:C)-ROW(C3)))))

This assumes that your data starts in cell C3, but you can change this to whatever cell your using. The match function searches for the last number cell in that column and it will sum the range from your starting cell to the last numbered cell in that column, so it will change dynamically.

If you are just looking for the last value, you can use this function instead.

=OFFSET(H3,MATCH(9.99999999999999E+307,OFFSET(H3,,,ROWS(H:H)-ROW(H3)))-1,0)

This one assumes that you're starting on H3. Adjust accordingly.

Upvotes: 1

Related Questions