View Single Post
  #6   Report Post  
Old September 21st 16, 01:45 PM posted to microsoft.public.excel.newusers
[email protected] is offline
external usenet poster
First recorded activity by ExcelBanter: Sep 2016
Posts: 2
Default formula to convert 15 minute to hourly data

I have the same thing. I have to average at 10 minutes interval. But i also have garbage values like 999. Will it work for that as well?


On Tuesday, August 12, 2008 at 2:24:07 AM UTC+2, Emily wrote:
I need a formula to convert 15 minute timestep data to hourly average (an
average of the four 15 minute data points for the hour).

My spreadsheet looks like this:
datetime in first cell data in second cell
1/1/00 00:00 2.2
1/1/00 00:15 2.0
1/1/00 00:30 1.9
1/1/00 00:45 2.0
1/1/00 01:00 2.4
1/1/00 01:15 2.2
1/1/00 01:30 2.1
1/1/00 01:45 2.0
1/1/00 02:00 1.8

etc..... I have about hundreds of rows of this type of data. At times there
may be missing data, but the correct time is there, there is just no data in
the cell. I would like a third column of data that would have the date/time
at the top of the hour and the fourth column to have the hourly average.

Any help appreciated.