How to create daily average from hourly data

**bubble_fisch** · 07-13-2011, 11:20 AM

Hi guys,

I'm looking at river levels in an area where I may buy property. The data given to me by the local council provides a reading of the river water level every 15 minutes (for several years). If possible I want to create daily averages and plot them in a graph so that I can see the average level for each day.

I've attached a sample of the data running over a few days (The entire data set covers a decade). I need a function for the "average volume" column so that I won't have to enter the data manually thousands of times.

Any help would be greatly appreciated

An Excel Novice

**alansidman** · 07-13-2011, 11:52 AM

I've used the subtotal function of excel to find the average for each day. Look at the attached.

Alan

**Paul** · 07-13-2011, 12:07 PM

Two options, one is very quick and the other uses an array formula, which could be slow with decades worth of data.

The formula option: Array formula (confirmed using CTRL+SHIFT+ENTER, not just ENTER):

=AVERAGE(IF(INT($A$2:$A$1000)=F2,$B$2:$B$1000))

Change the reference to row 1000 to your last used row.

Other Option: Pivot Table

Select your data in columns A:B and click Insert > PivotTable. Drag the DateTime field to the Row Labels area. Drag the SG field to the Values area. Right-click on any of the DateTime entries and choose Group... and select Days so it is highlighted (you may need to de-select any other options). Right-click in the Values area of the pivot table and choose Summarize Data By.. > Average.

That should do the trick.

**bubble_fisch** · 07-13-2011, 12:08 PM

Alan

Many many thanks for your help. Your assistance has been really useful for me!

**bubble_fisch** · 07-13-2011, 12:21 PM

Paul

- Many thanks - I'm now beginning to get great results with the pivot table.... so much so that my computer is complaining about too much unique data input!

Thanks again Alan and Paul

**skchauhan** · 01-27-2015, 02:50 AM

Dear Paul

Thanks a million for you help

**Hommy** · 02-03-2016, 02:21 PM

Just want to say thanks for the wealth of information! I have been doing them manually ever since

How to create daily average from hourly data

LinkBack

Thread Tools

Rate This Thread

Display

How to create daily average from hourly data

Re: How to create daily average from hourly data

Re: How to create daily average from hourly data

Re: How to create daily average from hourly data

Re: How to create daily average from hourly data

Re: How to create daily average from hourly data

Re: How to create daily average from hourly data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions