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
"(E-Mail Removed)" wrote:
> 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?
>
|
Similar Threads
