+ Reply to Thread
Results 1 to 6 of 6

How to average data by year?

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    Washington, D.C.
    MS-Off Ver
    Excel 2007
    Posts
    5

    How to average data by year?

    I have a spreadsheet of about 8000 rows of data and I need to average a column of data by the year in the correponding column. I would like to be able to drag the formula down to cover all years in the dataset. Any help would be greatly appreciated.

    Ex.

    C D E
    2007 2 Column of averages by year
    2007 1
    2007 3
    2006 2
    2006 4
    2006 1
    2005 2
    2005 3

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to average data by year?

    Hi Ryleigh,

    Welcome to the forum.

    You can use the below formula:-

    =AVERAGEIF($C$2:$C$8,$C2,$D$2:$D$8)

    See attached:- Average by year.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    07-11-2012
    Location
    Washington, D.C.
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How to average data by year?

    Thnaks DILIPandey,

    This is great. How would you return only one annual average in the new column to graph? I entered the criteria manually, instead of referencing the year cell, for the first few years (1980,1981,1982) and selected those cells to pull down to continue the vector to the final year (2010). However, this just caused the entered years to repeat (1980,1981,1982,1980,1981,1982).

    =AVERAGEIF($D$2:$D$8093,1980,$A$2:$A$8093)
    =AVERAGEIF($D$2:$D$8093,1981,$A$2:$A$8093)
    =AVERAGEIF($D$2:$D$8093,1982,$A$2:$A$8093)

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to average data by year?

    Hi Ryleigh,

    In that case, I would suggest to use Pivot Table and chart, see attached :-
    Average by year.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    07-11-2012
    Location
    Washington, D.C.
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How to average data by year?

    Thanks DILIPandey, worked great.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to average data by year?

    You are welcome Ryleigh...

    Cheers

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1