Converting monthly data into yearly averages

You asked to have the formula automatically increment to include
successive groups of twelve cells.

With
B2:b100 containing amounts

Try this:

Please Login or Register  to view this content.

Copy that formula down.

Note: I typically avoid volatile formulas (like OFFSET) because they
recalculate whenever any cell in the workbook recalcs. That can
be a problem in large, sluggish workbooks. If that's an issue with
your workbook....
This longer formula is NOT volatile:

Please Login or Register  to view this content.

Hopefully, though, your data list includes a column indicating year and month. That would be fairly easy to formulate. Let us know if that's your actual situation and we'll provide a different formula.


Is that something you can work with?

With
Col_A containing your year-mth ref
and
Col_B containing your amounts

Try this:

C2: (a year....eg 1959)
D2: =SUMIF($A$2:$A$1000,C2&"*",$B$2:$B$1000)

EDITED TO INCLUDE THE FOLLOWING:
I just realized that you asked for *averages*...not sums.
Consequently, this calculates the average:
C2: (a year....eg 1959)
D2: =SUMIF($A$2:$A$1000,C2&"*",$B$2:$B$1000)/COUNTIF($A$2:$A$1000,C2&"*")


Does that help?