Average Daily Balance
 

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?

Answer: Average Daily Balance
 

Yes, there is a function in Excel that can help you calculate the average daily balance for a selected range of dates. Here are the steps to do it:

1. First, make sure that your spreadsheet has a column for the date, a column for the transaction amount, and a column for the running balance.
2. Next, select the range of dates for which you want to calculate the average daily balance.
3. In a new cell, enter the formula "=AVERAGEIF(date_range,"="&start_date,balance_rang e)-AVERAGEIF(date_range,""&end_date,balance_range)" (without the quotes).
4. Replace "date_range" with the range of cells that contains the dates, "start_date" with the first date of the selected range, "end_date" with the last date of the selected range, and "balance_range" with the range of cells that contains the running balance.
5. Press Enter to calculate the average daily balance for the selected range of dates.

The formula works by first calculating the average balance for all dates that are greater than or equal to the start date, and then subtracting the average balance for all dates that are greater than the end date. This gives you the average daily balance for the selected range of dates.

Average Daily Balance
 

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.
--
Regards,
Fred


"pwrichcreek" wrote in message
...
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?

Average Daily Balance
 

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)



"pwrichcreek" wrote in message
...
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?

Average Daily Balance
 

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=E 1)*(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


"pwrichcreek" wrote:

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?

Average Daily Balance
 

On Feb 18, 5:55 am, pwrichcreek
wrote:
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.

Average Daily Balance
 

Each of your replies will be helpful.

Thanks, Phil

"pwrichcreek" wrote:

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?