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$44645>0),'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$44645>0),'Raw
Data'!S$2:S$44645)))
Bookmarks