Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I am trying to graphically determine an estimated projected date in the
future that an account will reach a certain balance, and need a formula to resolve this. Because the amounts being credited are random in date and amount, I need to simplify things by assuming that the next amount credited will be a mean average of all credits to the account spread over the time since the first payment. Since the data is organised one row per day, each day should show the average of all payments made so far since day one, and the same each day in the future, so we can create a graph of projected balance on any given future date. This will change over time, depending on the amount and frequency of payment. In column A I have dates, in B a balance, which is calculated as the sum of all credits so far and column C holds payments to the account. Example 1 Jan 10------0------0 2 Jan 10------5------5 3 Jan 10------5------0 4 Jan 10-----15-----10 5 Jan 10-----15------0 6 Jan 10-----22------7 7 Jan 10-----28------6 ...... Column D will hold the calculation for the projection, this will be graphed and the resulting line will be used to calculate the projected date when it intersects with the projected amount. Row 1 has column headings and in D2 I have =AVERAGE($C$2:C2) and then =B2+AVERAGE($C$2:C3), =B3+AVERAGE($C$2:C4) ...etc But currently my problem is that although the formula calculates the average, it hasn't (as yet) got figures for amounts credited on future dates, since the payments are irregular, the average decreases going down the column (since an average spread over a greater number of days will decrease), where I would like it to remain the same until the day actually passes and nothing has been credited. Any ideas? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
George -
First, your average formula is wrong, so that needs to be fixed. The average formula in column D should be: =B2/(CELL("row",A2)-1) (Add this and drag it down the column.) I assume you have column A populated with the dates as far down as you need. I assume you have a formula in column B to add the value of column C to the prior balance. Now what you need is to populate column C where it has not been entered yet. In cell C9 (assuming C2 to C8 has been filled in per your example), enter this: =D8 (This is the average to date, and what you want to add until you know an actual value.) Drag this formula down the column. Done! As you enter new values in column C, the formulas will update. You can decide if you want to round the averages or just the additions - you can play with that and see. -- Daryl S "George" wrote: I am trying to graphically determine an estimated projected date in the future that an account will reach a certain balance, and need a formula to resolve this. Because the amounts being credited are random in date and amount, I need to simplify things by assuming that the next amount credited will be a mean average of all credits to the account spread over the time since the first payment. Since the data is organised one row per day, each day should show the average of all payments made so far since day one, and the same each day in the future, so we can create a graph of projected balance on any given future date. This will change over time, depending on the amount and frequency of payment. In column A I have dates, in B a balance, which is calculated as the sum of all credits so far and column C holds payments to the account. Example 1 Jan 10------0------0 2 Jan 10------5------5 3 Jan 10------5------0 4 Jan 10-----15-----10 5 Jan 10-----15------0 6 Jan 10-----22------7 7 Jan 10-----28------6 ...... Column D will hold the calculation for the projection, this will be graphed and the resulting line will be used to calculate the projected date when it intersects with the projected amount. Row 1 has column headings and in D2 I have =AVERAGE($C$2:C2) and then =B2+AVERAGE($C$2:C3), =B3+AVERAGE($C$2:C4) ...etc But currently my problem is that although the formula calculates the average, it hasn't (as yet) got figures for amounts credited on future dates, since the payments are irregular, the average decreases going down the column (since an average spread over a greater number of days will decrease), where I would like it to remain the same until the day actually passes and nothing has been credited. Any ideas? . |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
As a matter of interest, what is the advantage in using CELL("row",A2)-1
rather than just ROW(A2)-1 or ROWS(A$2:A2) ? -- David Biddulph "Daryl S" wrote in message ... George - First, your average formula is wrong, so that needs to be fixed. The average formula in column D should be: =B2/(CELL("row",A2)-1) (Add this and drag it down the column.) I assume you have column A populated with the dates as far down as you need. I assume you have a formula in column B to add the value of column C to the prior balance. Now what you need is to populate column C where it has not been entered yet. In cell C9 (assuming C2 to C8 has been filled in per your example), enter this: =D8 (This is the average to date, and what you want to add until you know an actual value.) Drag this formula down the column. Done! As you enter new values in column C, the formulas will update. You can decide if you want to round the averages or just the additions - you can play with that and see. -- Daryl S "George" wrote: I am trying to graphically determine an estimated projected date in the future that an account will reach a certain balance, and need a formula to resolve this. Because the amounts being credited are random in date and amount, I need to simplify things by assuming that the next amount credited will be a mean average of all credits to the account spread over the time since the first payment. Since the data is organised one row per day, each day should show the average of all payments made so far since day one, and the same each day in the future, so we can create a graph of projected balance on any given future date. This will change over time, depending on the amount and frequency of payment. In column A I have dates, in B a balance, which is calculated as the sum of all credits so far and column C holds payments to the account. Example 1 Jan 10------0------0 2 Jan 10------5------5 3 Jan 10------5------0 4 Jan 10-----15-----10 5 Jan 10-----15------0 6 Jan 10-----22------7 7 Jan 10-----28------6 ...... Column D will hold the calculation for the projection, this will be graphed and the resulting line will be used to calculate the projected date when it intersects with the projected amount. Row 1 has column headings and in D2 I have =AVERAGE($C$2:C2) and then =B2+AVERAGE($C$2:C3), =B3+AVERAGE($C$2:C4) ...etc But currently my problem is that although the formula calculates the average, it hasn't (as yet) got figures for amounts credited on future dates, since the payments are irregular, the average decreases going down the column (since an average spread over a greater number of days will decrease), where I would like it to remain the same until the day actually passes and nothing has been credited. Any ideas? . |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() "Daryl S" wrote in message ... George - First, your average formula is wrong, so that needs to be fixed. The average formula in column D should be: =B2/(CELL("row",A2)-1) (Add this and drag it down the column.) I assume you have column A populated with the dates as far down as you need. I assume you have a formula in column B to add the value of column C to the prior balance. Now what you need is to populate column C where it has not been entered yet. In cell C9 (assuming C2 to C8 has been filled in per your example), enter this: =D8 (This is the average to date, and what you want to add until you know an actual value.) Drag this formula down the column. Done! Drag this formula down column C? But isn't this the row where he is entering the amounts paid into the account? Shouldn't you mean D instead of C and vice versa in the above? He said he wanted the average amount in D. As you enter new values in column C, the formulas will update. You can decide if you want to round the averages or just the additions - you can play with that and see. -- Daryl S "George" wrote: I am trying to graphically determine an estimated projected date in the future that an account will reach a certain balance, and need a formula to resolve this. Because the amounts being credited are random in date and amount, I need to simplify things by assuming that the next amount credited will be a mean average of all credits to the account spread over the time since the first payment. Since the data is organised one row per day, each day should show the average of all payments made so far since day one, and the same each day in the future, so we can create a graph of projected balance on any given future date. This will change over time, depending on the amount and frequency of payment. In column A I have dates, in B a balance, which is calculated as the sum of all credits so far and column C holds payments to the account. Example 1 Jan 10------0------0 2 Jan 10------5------5 3 Jan 10------5------0 4 Jan 10-----15-----10 5 Jan 10-----15------0 6 Jan 10-----22------7 7 Jan 10-----28------6 ...... Column D will hold the calculation for the projection, this will be graphed and the resulting line will be used to calculate the projected date when it intersects with the projected amount. Row 1 has column headings and in D2 I have =AVERAGE($C$2:C2) and then =B2+AVERAGE($C$2:C3), =B3+AVERAGE($C$2:C4) ...etc But currently my problem is that although the formula calculates the average, it hasn't (as yet) got figures for amounts credited on future dates, since the payments are irregular, the average decreases going down the column (since an average spread over a greater number of days will decrease), where I would like it to remain the same until the day actually passes and nothing has been credited. Any ideas? . |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() -- Daryl S "John Smith" wrote: In cell C9 (assuming C2 to C8 has been filled in per your example), enter this: =D8 (This is the average to date, and what you want to add until you know an actual value.) Drag this formula down the column. Done! Drag this formula down column C? But isn't this the row where he is entering the amounts paid into the account? Shouldn't you mean D instead of C and vice versa in the above? He said he wanted the average amount in D. Yes, I did mean down column C. If you want to create another column instead, you can, but then you probably need another column for the amounts paid to date also. More columns and duplication than I would want, but it would be OK to do. When you know an actual amount paid, you would over-write the formula in column C with the real data. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
David -
Yes, Row(A2) would work as well as Cell("row",A2). -- Daryl S "David Biddulph" wrote: As a matter of interest, what is the advantage in using CELL("row",A2)-1 rather than just ROW(A2)-1 or ROWS(A$2:A2) ? -- David Biddulph "Daryl S" wrote in message ... George - First, your average formula is wrong, so that needs to be fixed. The average formula in column D should be: =B2/(CELL("row",A2)-1) (Add this and drag it down the column.) I assume you have column A populated with the dates as far down as you need. I assume you have a formula in column B to add the value of column C to the prior balance. Now what you need is to populate column C where it has not been entered yet. In cell C9 (assuming C2 to C8 has been filled in per your example), enter this: =D8 (This is the average to date, and what you want to add until you know an actual value.) Drag this formula down the column. Done! As you enter new values in column C, the formulas will update. You can decide if you want to round the averages or just the additions - you can play with that and see. -- Daryl S "George" wrote: I am trying to graphically determine an estimated projected date in the future that an account will reach a certain balance, and need a formula to resolve this. Because the amounts being credited are random in date and amount, I need to simplify things by assuming that the next amount credited will be a mean average of all credits to the account spread over the time since the first payment. Since the data is organised one row per day, each day should show the average of all payments made so far since day one, and the same each day in the future, so we can create a graph of projected balance on any given future date. This will change over time, depending on the amount and frequency of payment. In column A I have dates, in B a balance, which is calculated as the sum of all credits so far and column C holds payments to the account. Example 1 Jan 10------0------0 2 Jan 10------5------5 3 Jan 10------5------0 4 Jan 10-----15-----10 5 Jan 10-----15------0 6 Jan 10-----22------7 7 Jan 10-----28------6 ...... Column D will hold the calculation for the projection, this will be graphed and the resulting line will be used to calculate the projected date when it intersects with the projected amount. Row 1 has column headings and in D2 I have =AVERAGE($C$2:C2) and then =B2+AVERAGE($C$2:C3), =B3+AVERAGE($C$2:C4) ...etc But currently my problem is that although the formula calculates the average, it hasn't (as yet) got figures for amounts credited on future dates, since the payments are irregular, the average decreases going down the column (since an average spread over a greater number of days will decrease), where I would like it to remain the same until the day actually passes and nothing has been credited. Any ideas? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Rolling Averages | Excel Discussion (Misc queries) | |||
Rolling Averages | Excel Discussion (Misc queries) | |||
Dynamic reporting rolling averages | Excel Worksheet Functions | |||
Need help creating 3-month rolling averages... | Excel Discussion (Misc queries) | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions |