12 Month Average
"Dewayne" wrote:
I calculate an average in cell G19 that includes the lastest 12 months
values. Each month I need to update the formula to exclude the first of the
last 12 months and include the new month. Currently I am doing this manually
each month. Is there a way to automatically update the formula to include
only the last 12 months in the average when new data is entered into a new
cell?
Suppose your initial data is in column E (e.g. E8:E19). Then put the
following formula into G19 and copy down through as many cells as you want:
=if(E19="", "", average(E8:E19))
When you copy down to G20 (e.g), the formula will be changed to:
=if(E20="", "", average(E9:E20))
When you enter data into E20, G20 will automatically show the average of the
12 cells ending with E20.
|