Average Daily Balance

[pwrichcreek]

I have a spreadsheet that lists checks and deposits in chronological sequence
and keeps a running balance of the account. Is there a function to calculate
the average daily balance for a selected range of dates?

 

[Fred Smith]

If you calculated the balance only on the last transaction of the day, Average
would do what you want, because it ignores blank cells. You could add a helper
column that calculates only the day's ending balance, then average that. If your
dates are in column A and balance in Column D, something like:

=if(a2=a1,"",d2)

would show the balance at the end of the day. Average that column.

 

[Bob Phillips]

this will calculate average of all amounts (in B2:B200) for dates (in
A2:A200) between 1st and 12th Jan inclusive

=AVERAGE(IF((A2:A200>=--"2007-01-01")*(A2:A200<=--"2007-01-12"),B2:B200)

This is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Coderre]

Try something like this:

With
A2:A50 containing chronological activity dates (with some gaps for days with
no activity) or blanks for future days
B2:B50 containing deposit and withdrawal activity for the corresponding dates

and...
D1: Start
E1: (the first date to include for the calcs....eg 01/01/2007)
D2: End
E2: (the last date to include for the calcs....eg 01/31/2007)


D3: ADB
This formula returns the ending balance of the prior period PLUS the ADB of
the target period activity
E3:
=SUMIF(A2:A51,"<"&E1,B2:B51)+SUMPRODUCT((A2:A51>=E1)*(A2:A51<=E2)*(E2-A2:A51+1)*B2:B51)/(E2-E1+1)

Example:
For this activity
29-Dec-06 1000
01-Jan-07 10
04-Jan-07 -5
07-Jan-07 100
10-Jan-07 10
13-Jan-07 -5
16-Jan-07 10
19-Jan-07 100
22-Jan-07 10
25-Jan-07 100
28-Jan-07 -5
31-Jan-07 100
03-Feb-07 10

The calculated ADB for Jan 2007 is: 1,165.65

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[joeu2004]

I have a spreadsheet that lists checks and deposits in chronological sequence
and keeps a running balance of the account. Is there a function to calculate
the average daily balance for a selected range of dates?

Don't forget to track when interest is paid to the account.

If you have transactions (checks/withdrawals and deposits) almost
daily, it might make sense to track the ending balance of every day,
even if there is no transaction on that day. (Note that weekends
count as a day.) In that case, simply use AVERAGE(B1:B31) to compute
the average daily balance, assuming B1:B31 contains the ending balance
of a 31-day month, for example.

If you have relatively few transactions per period, the average daily
balance is the sum of the each new balance times the number of days
that balance remains unchanged, all divided by the total days.
Assuming transactions are in consecutive rows, the average daily
balance can be computed as follows:

=sumproduct(A2:A11 - A1:A10, B1:B10) / (A11-A1)

That assumes you always have a "transaction" on the first and last
dates of the period, even if there is actual transaction. A1:B1 is
the end date and balance of the previous period; A10:B10 is the end
date and balance of the current period; and A11 is the start date of
the next period.

 

[pwrichcreek]

Each of your replies will be helpful.

Thanks, Phil