Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: How do you calculate averages by month?

  1. #1
    New Member
    Join Date
    Jun 2011
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do you calculate averages by month?

    Hello,

    I have two columns of data: column A contains the date and time in the form dd/mm/yyyy. Column B contains a number value. All the times are in order, so column A looks like:

    01/01/2007
    02/01/2007
    03/01/2007

    I have data from 2007 to 2010. I need to calculate an average daily value (in column B) for each month, and display it in column C. So, for January, I need to calculate the average of 31 days, February, 28 days, etc.

    Is there a function I can use to do this? I've been trying the AVERAGEIF formula but can't get it to work. Any ideas would be much appreciated!

    Many thanks,
    Caitlin

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    Have you tried a PivotTable, and group by month? Choosing a field setting of Average instead of Sum, that is.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    Ok. I've never done a pivot table before. Will that still display the data in 2 columns?

    I need to have the month in one column and the average in the column next to it. I need to graph the data afterwards.

    Thanks!
    Caitlin

  4. #4
    New Member
    Join Date
    May 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    Here is something that you could take a look at:

    Code:
    =AVERAGE(IF(MONTH('ColA')='MonthNumber','ColB'))
    Once code is entered, pressed ctrl+alt+enter to make it an array.

  5. #5
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    Quote Originally Posted by caitlin View Post
    Ok. I've never done a pivot table before. Will that still display the data in 2 columns?

    I need to have the month in one column and the average in the column next to it. I need to graph the data afterwards.

    Thanks!
    Caitlin
    Here's an example of a part of some data with a pivottable next to it, grouped by month and year, with the field setting set to average.

    Sheet16

     ABCDEF
    1DateValue Average of Value  
    201/01/2007123 YearsDateTotal
    302/01/200765 2007Jan98.74
    403/01/200722  Feb90.50
    504/01/200783  Mar86.61
    605/01/200734 Grand Total 92.00
    706/01/2007148    
    807/01/2007138    
    908/01/2007129    
    1009/01/2007153    
    1110/01/2007148    
    1211/01/200776    
    1312/01/200797    
    1413/01/2007102    


    Excel tables to the web >> Excel Jeanie HTML 4


    Add the date to the row area of the pivottable, and then right-click that area and choose "Group". Have a try and see how you do.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  6. #6
    New Member
    Join Date
    Jun 2011
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    Thanks a million! You just saved me buckets of time.
    Best,
    Caitlin

  7. #7
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    That's great! Glad to help.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  8. #8
    New Member
    Join Date
    Jun 2011
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    One more thing - I want to do the same basic operation, but this time I want to calculate the average by hour rather than by month. My data looks like:

    Column A:
    01/01/2007 0:00
    01/01/2007 0:15
    01/01/2007 0:30
    01/01/2007/0:45

    Column B:
    6.5
    8
    7.2
    8.5

    I want to calculate an average of the Column B values by hour, and then by day and by month. I'm using a pivot table, which calculates the daily and monthly averages fine. But when I try grouping by hour, it gives me an average of ALL of the 12:00 am values over the year, and not just on one day. I want my data to look like:

    Column A:

    01/01/2007 0:00
    01/01/2007 1:00
    01/01/2007 2:00
    01/01/2007 3:00

    With the averages in Column B.

    Thanks very much,
    Caitlin

  9. #9
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you calculate averages by month?

    You have to group by Year and Month and Days and Hours.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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