J
John Smith
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.
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.