Daily Average of 50,000 data points, different number of data points each day

[nfg05]

So here's my problem:

I have about 200,000 rows that look like this:

date|trade size|tradevolume
20020101|3,000|100,000
20020101|7,000|140,000
20020101|1,000|120,000
20020101|4,000|170,000
20020101|3,000|130,000
20020101|3,000|170,000
20020101|2,000|190,000
...
20021031|1,000|200,000

What I need is a DAILY average trade size, so that Excel averages all the sizes with date 20020101, 20020102, etc and spits out an individual average trade size for each day. Then I need the median of all those daily average trade sizes. It's especially hairy because the data is spread across multiple sheets since Excel 2003 (the version I'm using) can only handle ~65,000 rows per sheet. Any guidance on how to do this would be appreciated.

There are 180 days so the whole thing needs to be automated, it's not practical to do the same procedure 180 times. Also, it's not like there are 1000 observations on day 1 and then 1000 observations on day 2. 20020101 may have 1,238 observations while 20020102 has 1,437 so I need to use the column with the date to group the trade sizes for averaging somehow.

[ChemistB]

I'm on Excel 2000 so I am not sure but can Excel 2003 handle pivot tables with data across multiple sheets? A pivot table can give you averages by day and min/max values. I think with 2003 you can set up formulas too (MEDIAN function). Hope that helps.

ChemistB

[Gary Brown]

If you use Data | Import External Data | Import New Dataase Query

you can create a new query using Microsoft Query. This is similar to the query tool in Access. A parameter query could be created to select the date required for your calculation. When the data is returned, it can be refreshed as many times as required.

A sample file would help, as I could create you a solution.

[nfg05]

Thank you all for your responses. I ended up figuring out a solution using the sumif and countif functions. Unfortunately, they don't work in 3D so I had to do it for each sheet, but it worked out in the end nonetheless.

[Domenic]

Thank you all for your responses. I ended up figuring out a solution using the sumif and countif functions. Unfortunately, they don't work in 3D so I had to do it for each sheet, but it worked out in the end nonetheless.

If you download and install the free add-in Morefunc.xll, you can use the THREED function.

Hope this helps!