Well, I'd suggest putting your data in MSDE/SQL Server/Access, then either
1) use queries to calculate your averages, or
2) use MS Query to pull in just the data you want to Excel and calc your
averages there.

This link walks you through how to pass query parameters from Excel into
Access to get back the specific rows you want

http://www.nickhodge.co.uk/gui/datam...taexamples.htm


"John G." wrote:

I have an excel file with 2 worsheets. The first worksheet called "Raw Data"
contains data captured at 1min intervals, approx ~13,000 rows (44,000 in the
future) and 25 columns. I want to average the data over 1 hr intervals. So
i've written a formula that uses an "IF" satement that finds all the data
taken in a particlar hour and averages it. The formula also staes that is
there is an error in the raw data it will return a "-9999" value for error
checking purposes. For me to run these calcualtions for one fo the 25
columns of raw data it takes approx. 5minutes. If i try to calc more than 1
column at a time it will crash my system. I have a substancial machine P4
with 2gb of ram. Can someone please look at the formula i'm using and let me
know if there is a faster more effeciant way of processing this data. I'
open to other programs as well (ie. access ).




=IF(ISERROR(AVERAGE(IF((DAY('Raw Data'!$B$2:$B$44645)=DAY($A2))*(HOUR('Raw
Data'!$A$2:$A$44645)=HOUR($A2))*('Raw Data'!S$2:S$446450),'Raw
Data'!S$2:S$44645)))=TRUE,-9999,AVERAGE(IF((DAY('Raw
Data'!$B$2:$B$44645)=DAY($A2))*(HOUR('Raw
Data'!$A$2:$A$44645)=HOUR($A2))*('Raw Data'!S$2:S$446450),'Raw
Data'!S$2:S$44645)))