How do you calculate averages by month?

caitlin

New Member
Joined
Jun 1, 2011
Messages
6
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Have you tried a PivotTable, and group by month? Choosing a field setting of Average instead of Sum, that is.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
ABCDEF
1DateValueAverage of Value
201/01/2007123YearsDateTotal
302/01/2007652007Jan98.74
403/01/200722Feb90.50
504/01/200783Mar86.61
605/01/200734Grand Total92.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
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.
 
Upvote 0
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
 
Upvote 0
You have to group by Year and Month and Days and Hours.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top