Automation of extenion of data range for average calculatiions with each new month comes i

Hi Forum,


I have the attahced data set.

It shows population, income …data for each month in 3 different financial years.
With the beginning of a new month, a SAS program runs and generate the value for the past month.
E.g. values for yellow hihglighted cells, which marks Jun 2015, are now avaiable, as we are now in Jul 2015.

Q:
When I enter the value for last monh, that is Jun 2015, I have to manuaaly re-adjust the average formula in "O" column.
For e.g., O3, O2…..up to O13 cell's equation have to be redjusted to capture Jth column values.
Would there be any method to automate this process.

Thanks

Mirisage

Assuming that the only thing with headers in Row 1 will be either blanks, the name of the month, or AVG ______, you can use this. It's one approach and there are plenty of others.

Enter this in O2 and expand downwards.
=AVERAGEIFS(2:2,$1:$1,"<>",$1:$1,"<>"&"AVG*")

Hi Gedwards913 and Daffodil11,

Many thanks to both of you for this great help.

Regards

Mirisa