John

Array formulas are notoriously slow. And when they include 45,000 data
points, well, you get what you get. I have a similar spreadsheet in that it
has hundreds of array formulas each looking at over 30k cells. My
experience with that sheet is the same as yours - it takes a long time.

You might consider using a pivot table, if you haven't already. If you can
put a formula on the Raw Data sheet that identifies each row as belonging to
a particular hour, you could pivot the data on that column with an AVERAGE
aggregate in the data section. I'll be refreshing that pivot table would
take only a few seconds.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

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)))