Average Daily Balance

[Brian]

This might be a simple question...but I seem to be struggling to obtain a
formula.
I have a spreadsheet with an opening balance and then multiple rows of
activtity (either deposits or withdrawls) and sometimes there is daily
activity....sometimes we can go weeks without activity
I'm trying to establish a formula that will give me the Average Daily
Balance in the account using the rows of data factoring in the Deposit/
Withdrawl amounts and the dates.
Any suggestions?

 

[Luke M]

First, we need to determine what the daily balance is each day. Assuming
starting balance is in F1, with dates in a2:a10, credits in b2:b10, and
debits in c2:c10

Create a list of in e2 downward
formula in F2 is:
=F1+SUMIF(A$2:A$10,E2,B$2:B$10)-SUMIF(A$2:A$10,E2,C$2:C$10)
Copy down as needed.

Running average balance then, in G2:
=AVERAGE(F$1:F2)
Copy down as needed

 

[Brian]

Thanks Luke....but is it possible to do the same thing in one cell without
adding the formula you listed in column E?

 

[Joe User]

I have a spreadsheet with an opening balance and then multiple
rows of activtity (either deposits or withdrawls)

It would be better to maintain the balance in a separate column next to the
account activity. And I presume you have the date of the account activity
in a separate column. For example, column A for date, column B for activity
(+deposit, -withdrawal), column C for balance after the activity.

Moreover, it would be better if you have only one row per day of activity;
i.e. =deposits-withdrawals. In other words, column B is __net__ activity on
a date in column A.

Then if A1 is the date of the previous month's ending balance and A10 is the
date of the current month's ending balance, the average daily balance is:

=SUMPRODUCT(A2:A10 - A1:A9, C2:C10) / (A10-A1)

formatted as some numeric format (e.g. Number) other than General. If it is
formatted as General, Excel insists on reformatting it as Date :-(.

This presumes that the formula in C2 is =C1+B2, which is copied down through
C10.

Note: Of course, A2 and A10 can be the ending dates of any two periods.
But the point is: you need an entry for the end of each period, even if
there is no activity on that date.


----- original message -----