Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.newusers




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. 
#2




Answer: formula to convert 15 minute to hourly data
To convert 15minute timestep data to hourly average, follow these steps:
That's it! You now have a third column with the datetime at the top of the hour and a fourth column with the hourly average.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.newusers




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 
#4
Posted to microsoft.public.excel.newusers




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 
#5
Posted to microsoft.public.excel.newusers




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 
#6
Posted to microsoft.public.excel.newusers




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 
#7
Posted to microsoft.public.excel.newusers




formula to convert 15 minute to hourly data
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 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. 
#8
Posted to microsoft.public.excel.newusers




formula to convert 15 minute to hourly data
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 On Tuesday, August 12, 2008 at 10:02:57 AM UTC+2, Dave Curtis wrote: 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 
#9




Lý thuyết v* b*i t*p ôn thi Chứng chỉ đại lý thuế v* h*nh nghề CPA 2016
https://www.youtube.com/watch?v=tBQyLVTCS9A Link video t*i liệu lý thuyết v* b*i t*p các môn ôn thi chứng chỉ h*nh nghề CPA v* chứng chỉ đại lý thuế, các bạn theo dõi nhé, tối về rảnh rỗi sẽ up dần trọn bộ t*i liệu các môn lên. Nãm nay mình cũng thi cả 2 chứng chỉ n*y, ai có góp ý gì thì comment dýới video để cùng ôn luyện nhé. Chúng ta cùng CỐ NHÊÊÊÊNNNN 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to convert half hourly data into hourly  Excel Discussion (Misc queries)  
convert angle minute to decimal  Excel Discussion (Misc queries)  
How to convert an elapsed time in minutes to Days hours and minute  Excel Discussion (Misc queries)  
convert time from 60 minute hour to 100 minute hour  Excel Discussion (Misc queries)  
convert 100 minute hour to a 60 minute hour  Excel Worksheet Functions 