How do you calculate averages by month?

[caitlin]

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

 

[GlennUK]

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

 

[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

 

[dbrown14]

Here is something that you could take a look at:

=AVERAGE(IF(MONTH('ColA')='MonthNumber','ColB'))

Once code is entered, pressed ctrl+alt+enter to make it an array.

 

[GlennUK]

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.

Excel Workbook
	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
Sheet16

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.

 

[caitlin]

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

 

[GlennUK]

That's great! Glad to help.

 

[caitlin]

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

 

[GlennUK]

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