Ever changing averages, depending on date.
 

I asked about this before, but didn't really help, so I'll try rephrasing

There are three columns, one for dates (Column A), another for amounts on
each date (Column B), including dates in the future which will be left as
default (0) until a value is entered on that date and another for rolling
average amounts (Column C), for dates past, present and future. Row 1 has
column headings and Row 2 is a spacer row. Data starts in Row 3

I am seeking a formula in the average column (first cell will be C3 and
replicated downwards) that will return an average between two dates and
populate that average for every date in the future, but as values of future
dates, which have not been entered yet will by default be 0 should not
affect the average until after that date occurs. Yet values entered for
future dates will not affect the averages for past dates.

Example-

After one day has passed, it will populate all the cells in the average
column with that value (as that is an average of one number).

After two days have passed, it will return an average of those two days for
the cell representing the average for the second date and the third date and
all dates in the future.

After twenty days have passed, it will return an average of all amounts
split over those 20 days for the cell representing the average for the
twentieth date and all dates in the future.

etc.

Ever changing averages, depending on date.
 

How about a mini example data set?
Bernard

"John Smith" wrote in message
...
I asked about this before, but didn't really help, so I'll try rephrasing

There are three columns, one for dates (Column A), another for amounts on
each date (Column B), including dates in the future which will be left as
default (0) until a value is entered on that date and another for rolling
average amounts (Column C), for dates past, present and future. Row 1 has
column headings and Row 2 is a spacer row. Data starts in Row 3

I am seeking a formula in the average column (first cell will be C3 and
replicated downwards) that will return an average between two dates and
populate that average for every date in the future, but as values of
future dates, which have not been entered yet will by default be 0 should
not affect the average until after that date occurs. Yet values entered
for future dates will not affect the averages for past dates.

Example-

After one day has passed, it will populate all the cells in the average
column with that value (as that is an average of one number).

After two days have passed, it will return an average of those two days
for the cell representing the average for the second date and the third
date and all dates in the future.

After twenty days have passed, it will return an average of all amounts
split over those 20 days for the cell representing the average for the
twentieth date and all dates in the future.

etc.

Ever changing averages, depending on date.
 

"Bernard Liengme" wrote in message
...
How about a mini example data set?
Bernard


28Jan2010---21---21
29Jan2010---37---29
30Jan2010---23---27
31Jan2010---39---30 (today)
01Feb2010----0---30
02Feb2010----0---30
02Feb2010----0---30

Ever changing averages, depending on date.
 

With your data starting in A1
In C1: + B1
In C2: =IF(B2,AVERAGE($B$1:B2),C1) 'note the absolute reference on B1
This is copied down the column
You might be more comfortable with =IF(B20, bit Excel will take any numeric
value as meaning TRUE in a formula such as =IF(B2, .....
best wishes
Bernard

"John Smith" wrote in message
...

"Bernard Liengme" wrote in message
...
How about a mini example data set?
Bernard


28Jan2010---21---21
29Jan2010---37---29
30Jan2010---23---27
31Jan2010---39---30 (today)
01Feb2010----0---30
02Feb2010----0---30
02Feb2010----0---30