Slow Formula

[Guest]

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

 

[Guest]

Hi john,

The simplest way to reduce the time needed for calc to 1/2 is to remove the
iserror clause. Then also try to use the following formula:
=average(if((text('Raw
Data'!$B$2:$B$44645,"yyyymmddhh")=text($A2,"yyyymmddhh"))*('Raw
Data'!S$2:S$44645>0),'Raw Data'!S$2:S$44645))

 

[Dick Kusleika]

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.

 

[Glen]

I wonder if I am understanding this correctly.
You have 25 columns, each with 13,000 rows of numeric data. If there
was an error in one of these cells its value would be -9999. And you
need to average all of these numbers together and leave the -9999 out
of the equation?

 

[Guest]

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/datamenu/dataexamples/externaldataexamples.htm