Compute average temperature if between dates

[MFR]

I need to compute the average temperature per week for multiple years of data with 48 data points per day. How do I get the average temp per week.

An example of the data available follows.

Date and Time (EDT) Temperature (°F)
2012-06-26 16:01:37 84.2
2012-06-26 16:31:37 84.02
2012-06-26 17:01:37 84.74
2012-06-26 17:31:37 84.92
2012-06-26 18:01:37 85.1
2012-06-26 18:31:37 84.92
2012-06-26 19:01:37 84.74
2012-06-26 19:31:37 84.74
2012-06-26 20:01:37 84.38
2012-06-26 20:31:37 84.2
2012-06-26 21:01:37 83.48
2012-06-26 21:31:37 82.58
2012-06-26 22:01:37 82.22
2012-06-26 22:31:37 82.04
2012-06-26 23:01:37 81.86
2012-06-26 23:31:37 81.5
2012-06-27 00:01:37 81.14
2012-06-27 00:31:37 80.6
2012-06-27 01:01:37 80.24
2012-06-27 01:31:37 79.7
2012-06-27 02:01:37 79.34
2012-06-27 02:31:37 78.8
2012-06-27 03:01:37 78.44
2012-06-27 03:31:37 78.08
2012-06-27 04:01:37 77.54
2012-06-27 04:31:37 77.18

[K m]

Why couldn't you have formula that averages every week worth of data {336 rows of data (48*7)}? ie =average(B1:B336)

To get a daily rolling average you would average 336 rows of data separate by 48 rows (1 day).

[MFR]

I do need to track averages by week and then chart those averages.

[Ron Coderre]

How about using a Pivot Table?

• Insert a column before your data
• Use this formula to determine which year-week the date/time is in
A2: =YEAR(B2)&"_"&WEEKNUM(B2)
(sample week will look like: 2011_51)

• Copy that formula down as far as you need
• A1: Week

Next, build the Pivot Table
• Select your data
• Insert.PivotTable.Table
...select a destination
...Drag "Week" to the Row Labels
...Drag the date/time heading to Values
...Click the dropdown for the values
...Select "Value Field Settings"
...Change the calculation to: Average

Click: OK

That will give you the average temperature by week.

Is that something you can work with?

[MFR]

I believe that will work. Thanks for the help.