If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average Daily Balance
This might be a simple question...but I seem to be struggling to obtain a
formula. I have a spreadsheet with an opening balance and then multiple rows of activtity (either deposits or withdrawls) and sometimes there is daily activity....sometimes we can go weeks without activity I'm trying to establish a formula that will give me the Average Daily Balance in the account using the rows of data factoring in the Deposit/ Withdrawl amounts and the dates. Any suggestions? |
#2
|
|||
|
|||
Average Daily Balance
First, we need to determine what the daily balance is each day. Assuming
starting balance is in F1, with dates in a2:a10, credits in b2:b10, and debits in c2:c10 Create a list of in e2 downward formula in F2 is: =F1+SUMIF(A$2:A$10,E2,B$2:B$10)-SUMIF(A$2:A$10,E2,C$2:C$10) Copy down as needed. Running average balance then, in G2: =AVERAGE(F$1:F2) Copy down as needed -- Best Regards, Luke M "Brian" wrote in message ... This might be a simple question...but I seem to be struggling to obtain a formula. I have a spreadsheet with an opening balance and then multiple rows of activtity (either deposits or withdrawls) and sometimes there is daily activity....sometimes we can go weeks without activity I'm trying to establish a formula that will give me the Average Daily Balance in the account using the rows of data factoring in the Deposit/ Withdrawl amounts and the dates. Any suggestions? |
#3
|
|||
|
|||
Average Daily Balance
Thanks Luke....but is it possible to do the same thing in one cell without
adding the formula you listed in column E? "Luke M" wrote: First, we need to determine what the daily balance is each day. Assuming starting balance is in F1, with dates in a2:a10, credits in b2:b10, and debits in c2:c10 Create a list of in e2 downward formula in F2 is: =F1+SUMIF(A$2:A$10,E2,B$2:B$10)-SUMIF(A$2:A$10,E2,C$2:C$10) Copy down as needed. Running average balance then, in G2: =AVERAGE(F$1:F2) Copy down as needed -- Best Regards, Luke M "Brian" wrote in message ... This might be a simple question...but I seem to be struggling to obtain a formula. I have a spreadsheet with an opening balance and then multiple rows of activtity (either deposits or withdrawls) and sometimes there is daily activity....sometimes we can go weeks without activity I'm trying to establish a formula that will give me the Average Daily Balance in the account using the rows of data factoring in the Deposit/ Withdrawl amounts and the dates. Any suggestions? . |
#4
|
|||
|
|||
Average Daily Balance
"Brian" wrote:
I have a spreadsheet with an opening balance and then multiple rows of activtity (either deposits or withdrawls) It would be better to maintain the balance in a separate column next to the account activity. And I presume you have the date of the account activity in a separate column. For example, column A for date, column B for activity (+deposit, -withdrawal), column C for balance after the activity. Moreover, it would be better if you have only one row per day of activity; i.e. =deposits-withdrawals. In other words, column B is __net__ activity on a date in column A. Then if A1 is the date of the previous month's ending balance and A10 is the date of the current month's ending balance, the average daily balance is: =SUMPRODUCT(A2:A10 - A1:A9, C2:C10) / (A10-A1) formatted as some numeric format (e.g. Number) other than General. If it is formatted as General, Excel insists on reformatting it as Date :-(. This presumes that the formula in C2 is =C1+B2, which is copied down through C10. Note: Of course, A2 and A10 can be the ending dates of any two periods. But the point is: you need an entry for the end of each period, even if there is no activity on that date. ----- original message ----- "Brian" wrote in message ... This might be a simple question...but I seem to be struggling to obtain a formula. I have a spreadsheet with an opening balance and then multiple rows of activtity (either deposits or withdrawls) and sometimes there is daily activity....sometimes we can go weeks without activity I'm trying to establish a formula that will give me the Average Daily Balance in the account using the rows of data factoring in the Deposit/ Withdrawl amounts and the dates. Any suggestions? |
Thread Tools | |
Display Modes | |
|
|