Extracting seasonal data from non-ordered time series

Hello,

I have hundreds of spreadsheets that have output data from climate computer modeling. The output data is sub-daily, where one day may have >20 output rows, but some days have only 1 row.

For example, my models start on 1/1/1892. The first day of output data (day 0-1) is 48 rows. The 5th day of output data (day 4-5) is 8 rows.

The models compute 30 years of input data (1/1/1982 - 12/31/2011), producing spreadsheets that are very large, some over 4 million rows.

My goal is to determine the median value of the data on a seasonal basis, so for December/January/February, March/Apr/May, Jun/Jul/Aug, and Sept/Oct/Nov for each year. Therefore getting 30 median values for each season, one for each year.

A few months ago I posted a similar question about the non-ordered time series, asking for a formula to determine yearly averages. I received great help with the following formula:
=IF(DATEVALUE("1/1/"&YEAR($A2+D$1))=($A2+$D$1),AVERAGE(INDEX($B$1:$B1,MATCH(9E+307,D$1:D1 )+1):$B2),"")
Where, the formula was entered in Cell D2, Column A is the day (e.g. 4.0321 days), Column B is the variable I needed averaged (e.g. 7.49E-05), and Cell D1 is the start date, 1/1/1982.

I attached a sample spreadsheet of the data with the above formula entered in Cell D2.

I'm wondering if there is another formula I could put into the spreadsheets to quickly get the median values of the data for each season.

Any help is much appreciated.

Thank You,
Zach

Hello Again,

I know this is a complicated question, so please post any clarification questions.

Thank You

The computer model uses time steps to solve the equations built within the modeling program. For example, Cell A2 equals 0.001, meaning that is 0.001 days into January 1st, 1982. January 1st goes all the way through Cell A49. Cell A50 equals 1, meaning that is the beginning of January 2nd, 1982.

The spreadsheet is a small portion of the total spreadsheet (due to size limitations on uploads), but the model runs for 30 years or 10956 days. Time equal to 10956 is December 30th, 2011. I know December has 31 days, but I had to use an even number of days in the model, so I couldn't use 10957.

The formula in Cell D2 was written to average the values in Column B on a yearly basis, which includes leap years. The first leap year is 1984.

What I'm looking to do is find the median of each season for each year. For example, the median value of Column B between the Time of 0 and 59 (that is January and February 1982), and then the median value of Column B between the Time of 60 and 151 (that is March, April and May 1982), etc...

This would be easy if each day had the same number of rows, but January 1, 1982, fills up row A2 through A49 (48 cells), and January 2, 1982 fills up row A50 through A98 (49 rows).

I hope this adds a little clarification. Thanks for your question, and please ask any more questions that arise.

Zach

I've attached an updated version of the spreadsheet with a few of the values I'm looking for in Column F. I can get the median values by clicking and dragging, but hoping there is a quicker way to do this, because I have over 140 of these spreadsheets.

oeldere,

Thank you for your help, I really appreciate it. There are a few errors in your spreadsheet. January stops at day 30, but it should go through January 31. The table gives monthly average value, but I need seasonal median value. I need the median value of December/January/February (but 1982 will only include January and February), March/April/May, June/July/August, and September/October/November for each year.

The updated spreadsheet attached above might help clarify.

Zach