Creating yearly averages from monthly data in Excel

microsoft-excel microsoft-excel-2010 worksheet-function

11,527

Your objective is to average 12 previous periods and post the result, but only if the period is a multiple of 12. Here is a way to do that copying a single formula down the entire data range (no need to position the formula every 12 months).

I'm assuming your Yearly Average is column C and the data starts in row 2 (header in row 1). The period is column A. Enter this formula in C13:

=IF(MOD(A13,12)=0,AVERAGE(B2:B13),"")

Then copy the cell and paste it in C14 through the end of the data rows (you can select and navigate to the end with a few clicks or use the PageDown key to select the entire range).

How It Works

Every cell in Column C (starting with the first place you need it), gets the formula, which is what allows you to easily paste it in the entire range rather than in selected cells. For periods that are a multiple of 12, the formula calculates the average of the 12 periods ending with that one. If the period is not a multiple of 12, it displays the cell as blank.

11,527

jcronin

Updated on September 18, 2022

Comments

jcronin over 1 year
My ultimate goal is to graph yearly averages of my data. I have monthly data, and I would only like to return the average for each 12 month period.
```
PERIOD    MONTHLY     YEARLY AVERAGE
1         10
2         17
3         14
4         22
...
12        25          18      
13        19
14        18
...
24        22          21 
```
This is what my data looks like, and I would like to create a yearly average for every 12 month period. I would only like a single value for each yearly average. Is there an easy way to do this?
- Admin almost 9 years
  
  Yes, there are very easy ways to do this in Excel. What formulae have you already tried? Hint: AVERAGE()
- Admin almost 9 years
  
  Yes, I have used AVERAGE(A2:A13), etc. The problem I have when I use that approach is all of the extra values that are included if I use fill->down with that equation.
- Admin almost 9 years
  
  Say your data starts on row 2, so the first year is rows 2-13. Put the year 1 value in C13. Select C2-C13, copy, paste into C14. Click End-Down Arrow to get to the bottom. Next cell, paste again.
- Admin almost 9 years
  
  Easier yet, select C2:C13 and drag down.
- Admin almost 9 years
  
  The Data set is fairly large, so I am looking for a way to do this without having to do it seperately for each period.
- Admin almost 9 years
  
  I realized where you were going with your other question on filtering. You can accomplish that without the filtering. Instead of a helper column, you can do everything in the Yearly Average column. I'll post an answer.
- Admin almost 9 years
  
  This is exactly what I was looking for. Thanks for explaining how it works as well, very informative and helpful.