A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » New Users to Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

formula to convert 15 minute to hourly data



 
 
Thread Tools Display Modes
  #1  
Old August 12th 08, 01:24 AM posted to microsoft.public.excel.newusers
Emily
external usenet poster
 
Posts: 40
Default formula to convert 15 minute to hourly data

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.
Ads
  #2  
Old August 12th 08, 09:02 AM posted to microsoft.public.excel.newusers
Dave Curtis
external usenet poster
 
Posts: 100
Default formula to convert 15 minute to hourly data

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
  #3  
Old August 12th 08, 11:09 AM posted to microsoft.public.excel.newusers
Dave Curtis
external usenet poster
 
Posts: 100
Default formula to convert 15 minute to hourly data

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
  #4  
Old August 13th 08, 01:21 AM posted to microsoft.public.excel.newusers
Emily
external usenet poster
 
Posts: 40
Default formula to convert 15 minute to hourly data

Thanks for your help, it works!

"Dave Curtis" wrote:

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

  #5  
Old May 20th 10, 12:18 AM posted to microsoft.public.excel.newusers
penny
external usenet poster
 
Posts: 1
Default formula to convert 15 minute to hourly data

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert half hourly data into hourly kippers Excel Discussion (Misc queries) 5 February 1st 07 11:27 AM
convert angle minute to decimal nobbyknownowt Excel Discussion (Misc queries) 2 December 21st 06 08:37 PM
How to convert an elapsed time in minutes to Days hours and minute Time Tracker Excel Discussion (Misc queries) 1 April 9th 06 03:40 AM
convert time from 60 minute hour to 100 minute hour Jboerding Excel Discussion (Misc queries) 2 July 6th 05 11:30 PM
convert 100 minute hour to a 60 minute hour Todd Excel Worksheet Functions 1 November 15th 04 07:14 PM


All times are GMT +1. The time now is 09:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.