Average the first nine numbers in a range.

[Chad]

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.

 

[JE McGimpsey]

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

 

[Chad]

I'll give an example of what the worksheet looks like to help clarify.

Date: Value: Average:

01/01/06 11500
......
01/15/06 12050
.......
01/30/06 12750 12000
........
12/31/06 13500 13000
..........
9/30/07 14000 12500
...........

So this is a rough example, but basically, every day from the beginning of
2006 until the currenty date would be in the far left column, and a
corresponding value would be listed in each cell of the middle column. Then
in the right column there is a monthly average calculated on the final day of
each month. So, idealy I could average all the monthly averages calculated in
2007, which would be nine cells, then do some average function with a count
function emedded in it, that would average the first nine numbers in the
entire 2006 monthly average range.

Any thoughts?