Rolling Averages?

[George]

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?

 

[Daryl S]

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.

 

[David Biddulph]

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) ?

 

[John Smith]

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.

 

[Daryl S]

--
Daryl S

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.

 

[Daryl S]

David -

Yes, Row(A2) would work as well as Cell("row",A2).