Calculating Average Daily Balances

[jacobfarino]

I have a simple checkbook ledger in Excel 2007.

I want to compute the Average Daily Balance. This is much harder than
it appears...

I have my entry dates in column C
I have my actual balance in column H
(I also have credits in column F, debits in column G, if it helps)

The checkbook ledger is active, so the actual balance may or may not
change on any given day; average daily balance should change every
day. I had thought about calculating the number of days from first
entry date to today() and dividing into the current balance, but this
does not account for all the days in between that have no actual
entries.

For example:

DATE BALANCE
10/29/08 100.00
11/2/08 9.00
11/10/08 45.00

The average daily balance is $40.75, calculated manually. Calculated
by doing the following math:
(100 * 4 days) + (9 * 8 days) + (45 * 4 days) / 16 days
tomorrow when i log in (11/14/08) the avg daily balance should read:
$41 assuming the balance hasn't changed.


How do I get this into an excel formula?

 

[Shane Devenshire]

Hi,

Suppose the first day of the month is on line 2 so the balance is in cell
H2, then in I2 enter the following formula:

=AVERAGE(H$2:H2)

Note the $ sign. Copy this formula down as far as you want. The latest
balance will show all the way down from the last day entered, so you can
modify the formula to read:

=IF(H2="","",AVERAGE(H$2:H2))

If this helps, please click the Yes button

Cheers,
Shane Devenshire