I am trying to average a data set for an instrument which recorded data for 20s every 15 minutes I would like to average those 20s intervals so that I get one data point every 15 minutes. I saw a previous post where a pivot table was used, however grouping doesn't work in this case because I need to average over the last 10s of the 59th minute with first 10s of the 00 minute. This also causes a problem for the midnight reading which prevents grouping by day because the end of one day and the beginning of the next are one data point. I have attached an example.
Last edited by ScottL; 11-28-2011 at 09:27 AM.
Can you post your expected results for the sample data you gave and specifics about how/why?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I attached an example of how I would like the averages to work. Because of how the device works it is much more accurate to take an average of 20 samples at 1 s intervals every 15 min.
To reiterate, the point is to take the 20 values generated every 15 mins and average them together to get 1 value for every 15 minutes.
Try in D2:
=IF(AND(MOD(MINUTE(A2),15)=0,SECOND(A2)=0),AVERAGE(INDEX(C:C,ROW()-10):INDEX(C:C,ROW()+9)),"")
copied down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks