Hello,
I have a large spreadsheet that contains for each 15 minute block of a year a data point. I have several days that need a particular time range to be averaged (give to me as a spreadsheet).
eg. If I needed all data points between 6:00 and 7:45 on June 19, 2010, I would need to average these data points:
...
...
19/06/2002 6:00 100 19/06/2010 6:15 100 19/06/2010 6:30 120 19/06/2010 6:45 344 19/06/2010 7:00 700 19/06/2010 7:15 12 19/06/2010 7:30 32 19/06/2010 7:45 77
I have many dates for which I have to average the data points for and the size of my data set is fairly large so I was wondering if there was a better way to do this. At the moment, I'm just finding the start time for each individual time block, calculating in my head how many cells I need to jump ahead, and then taking the average of the resulting cell range.
I've done some searching on what functions might help me along with my task. The closest I've found is on this page and I've also considered SUMIF (primary range would be dates, sum range would be data points), but I'm not sure how to incorporate the time range.
However, I would like to be able to have a formula that can take a day and time range and then pull out all of the relevant cells and then average them. The time range is fairly consistent for 80% of the dates that I need to work with.
I'm also not sure if this task would be easier with a formula or a VBA script (my scripting knowledge is somewhat limited, but I've written a few simple ones from scratch before)
If I could get some help with how to approach this job it would be greatly appreciated.
Thanks in advance.
Bookmarks