formula to convert 15 minute to hourly data

[Emily]

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.

[Dave Curtis]

Hi,
The following seems to work even if there is missing data.
Assuming your data starts in row 2, enter the following formula in C2 and
drag down

=IF(MOD(ROW()+2,4)=0,VLOOKUP(ROUNDDOWN(A2*24,0)/24,$A$2:$B$10,2),"")

and the following formula in D2 and drag down

=IF(MOD(ROW()+2,4)=0,AVERAGE(B2:B5),"")

Dave

url:http://www.ureader.com/msg/10356248.aspx

[Dave Curtis]

Hi again,
Correction to previous reply. To get the time in Column C,
put the following into C2 and drag down.

=IF(MOD(ROW()+2,4)=0,ROUNDDOWN(A2*24,0)/24,"")

Dave

url:http://www.ureader.com/msg/10356248.aspx

[Emily]

Thanks for your help, it works!

[penny]

Hi,
I tried to use this and it works but how can I get the B2:B5 range to begin
at the ending cell (i.e. B5) and calculate each subsequent range accordingly.
My problem is that if I am averaging every 15 minute data for each hour I
would like for it to calculate B2:B5, B5:B8, B8:B11,etc...Any help would be
much appreciated. Thank you for taking the time to look at this.

url:http://www.ureader.com/msg/10356248.aspx

[Luke M]

This formula should work:
=AVERAGE(OFFSET($B$2,(ROW(A1)-1)*3,,4))
this averages range B2:B5
copying down will automatically change it to:
=AVERAGE(OFFSET($B$2,(ROW(A2)-1)*3,,4))
which averages range B5:B8

--
Best Regards,

Luke M
"penny" <ab...@hotmail.com> wrote in message news:...
> Message-ID: <32af0b05d7ed4af8...@newspe.com>
> X-Mailer: http://www.umailcampaign.com, ip log:68.45.155.120
> Newsgroups: microsoft.public.excel.newusers
> NNTP-Posting-Host: 22.bb.5446.static.theplanet.com 70.84.187.34
> Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl!newspe.com
> Lines: 1
> Xref: TK2MSFTNGP01.phx.gbl microsoft.public.excel.newusers:69083

[zargha...@gmail.com]

Hi
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?

Regards

[zargha...@gmail.com]

Hi
I have a data in the following format

datetime Value
1/1/2012 0:00 10.78
1/1/2012 0:10 11.95
1/1/2012 0:20 12.91
1/1/2012 0:30 12.98
1/1/2012 0:40 12.53
1/1/2012 0:50 12.97
1/1/2012 1:00 12.41
1/1/2012 1:10 11.85
1/1/2012 1:20 12.2
1/1/2012 1:30 12.36
1/1/2012 1:40 12.52
1/1/2012 1:50 12.61
1/1/2012 2:00 12.81

I want the result like
datetime Value
1/1/2012 0:00 Mean value of 6 values
1/1/2012 1:00 Mean value of hour

Also there r some NaN values like 999 and i want it to be ignored.

I would be very thankful for your help. Thanks

Regards