0

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?

  • Yes, there are very easy ways to do this in Excel. What formulae have you already tried? Hint: AVERAGE() – Mike Fitzpatrick Jul 13 '15 at 6:07
  • 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. – jcronin Jul 13 '15 at 6:21
  • 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. – fixer1234 Jul 13 '15 at 6:30
  • Easier yet, select C2:C13 and drag down. – Scott Jul 13 '15 at 6:40
  • 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. – jcronin Jul 13 '15 at 6:47
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.