Originally Posted by
Prof Sick
I'm after the average daily balance in the account between two periods
And to that end, it would help to create a column (D) with the daily balance. I believe that is:
D2: =C2
D3: =D2+C3
Then, the simple calculation of the average daily balance between 1/1/2007 and 1/25/2007 is:
=(SUMPRODUCT(A4:A13 - A3:A12, D3:D12) + D13) / (F2 - F1 + 1)
To allow for variable dates in F1 and F2, the following formulas would help:
G1: =MATCH(F1, $A$1:$A$1000, 0)
G2: =MATCH(F2, $A$1:$A$1000, 0)
Then the formula in F3 can be:
=(SUMPRODUCT(INDEX(A:A, G1+1):INDEX(A:A, G2) - INDEX(A:A, G1):INDEX(A:A, G2-1), INDEX(D:D, G1):INDEX(D:D, G2-1)) + INDEX(D:D, G2)) / (F2- F1 + 1)
or
=(SUMPRODUCT(OFFSET(A1, G1, 0, G2-G1) - OFFSET(A1, G1-1, 0, G2-G1), OFFSET(D1, G1-1, 0, G2-G1)) + INDEX(D:D,G2)) / (F2 - F1 + 1)
Generally, INDEX:INDEX is better because OFFSET is a "volatile" function.
We can confirm the result by creating a table in J1:J25 with the dates between 1/1/2007 and 1/25/2007, and enter the following formula into K1:K25:
K1: =VLOOKUP(J1, $A$2:$D$16, 4, 1)
Then the average daily balance is:
=SUM(K1:K25) / (F2 - F1 + 1)
Bookmarks