View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.newusers
[email protected] zarghamali086@gmail.com is offline
external usenet poster
 
Posts: 2
Default 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