Conditional calculations for arbitrary time windows in massive data set

Hello,

I have a very large data set comprised of hourly temperature, degree days (an integrated measurement of time and temperature) and solar radiation measurements at a site in Greenland from August 1995 to the end of 2009. During the summers of each year from 1996 to 2009, biological sampling events occurred approximately 7 days apart (sometimes, 5,6,8 days apart) from June until the snows come in August or September. For each year, for each interval preceding each sampling event, I need to calculate the sum and average of degree days, average temperature, temperature variance, average solar radiation, and solar radiation variance. After stabbing at it for a while, I have no idea how to calculate those statistics for the variables in question apart from doing each stat for each year for each interval individually, which I hope not to have to do. I thought there may be a way to simply add a column for each of the stats in question and write a lengthy conditional that would spit out the appropriate values for intervals bounded by certain stipulated dates, but am now not so sure. Any ideas? I am attaching a very small sample from the datasheet so that you might see its layout. Air_temperature_200cm, TDH (thawing degree hours), and In_sw_rad (solar radiation) are the three variables of interest. Thanks for any help you might be able to provide

Not my area, but on viewing your file I'm left asking where's the event info? In order to sum, average, and determine variance of a limited set of samples within a large database of samples, one has to know the criteria to extract the appropriate samples.

Originally Posted by jhren

Not my area, but on viewing your file I'm left asking where's the event info? In order to sum, average, and determine variance of a limited set of samples within a large database of samples, one has to know the criteria to extract the appropriate samples.

Hi, thanks for your reply. I wasn't sure if the answer would be best delivered in general terms, and so I did not include the dates that frame the intervals in question, but in retrospect, I can see how they may have helped make concrete the issue. I am attaching the subset of the data inclusive of the year 1996, and the dates that determine the twelve intervals. Column "O" contains the dates in day of year format. The first date listed is the date that begins the interval; the date below it is the first day of the next interval (i.e. the first interval of interest is 162 to 168, inclusive, the next is 169 to 175, inclusive). Hopefully this helps. Thanks for your time!

See if this works for you. Did sum, average and variance of air temp using SUMIFS, AVERAGEIFS, and VAR.S... which I'm not sure if 2008 has all of them (and working correctly). If they work, you should be able to glean the formulas for the other fields. If not, you know where to find me/us

Also, I wasn't sure whether you wanted to end of year or not, and because I used "<" next interval date as criteria, I added an extra day at the end of the column (367, and O15 is linked to it). If that presents a problem, we'll have to work something else out (easiest would be to just modify the last-row formula)...

That worked, many thanks!

Your welcome!!!


Please mark thread as solved (click thread tools at top, last option).