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