Thanks:  0
Likes:  0

# Thread: How do you calculate averages by month?

1. ## 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. ## 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.

3. ## 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. ## 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. ## Re: How do you calculate averages by month?

Originally Posted by caitlin
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

 A B C D E F 1 Date Value Average of Value 2 01/01/2007 123 Years Date Total 3 02/01/2007 65 2007 Jan 98.74 4 03/01/2007 22 Feb 90.50 5 04/01/2007 83 Mar 86.61 6 05/01/2007 34 Grand Total 92.00 7 06/01/2007 148 8 07/01/2007 138 9 08/01/2007 129 10 09/01/2007 153 11 10/01/2007 148 12 11/01/2007 76 13 12/01/2007 97 14 13/01/2007 102

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.

6. ## Re: How do you calculate averages by month?

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

8. ## 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. ## Re: How do you calculate averages by month?

You have to group by Year and Month and Days and Hours.

## User Tag List

#### Posting Permissions

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