One way:

Assuming range is B2:M2:

=AVERAGE(OFFSET(B2,,,1, MONTH(TODAY())-1)

Note that this won't work once January rolls around.

IF A2 has 2006, then this should work:

=AVERAGE(OFFSET(B2,,,1,MIN(12,DATEDIF(DATE(A2+1,1, 0),TODAY(),"M"))))

though I'm pretty sure it's not the most efficient way

In article ,
Chad wrote:

I am trying to compare the average for the first nine months of 2007, to
2006; once october is over this will change to the first 10 months of 2007
and so on. I have an automated cell that counts the number of months that
have passed in 2007, and another that takes the average of the the 2007
results. The problem is that I need to automate a cell to average the first 9
months of 2006, and then automatically change to 10 months and then to 11
months as time passes. I'm looking for a function that is something like:
average first 9 values, so I can input a range of values and then a formula
that will determine how many of those values to average. Does anyone know how
I can accomplish this? If my explanation isn't clear, please let me know.

Thanks.