Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Formula
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))) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Formula
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,"yyyymm ddhh"))*('Raw Data'!S$2:S$446450),'Raw Data'!S$2:S$44645)) "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))) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Formula
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))) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Formula
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Formula
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula -too slow | Excel Discussion (Misc queries) | |||
Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
sumproduct formula to slow | Excel Worksheet Functions | |||
Formula Yields Slow response | Excel Programming | |||
Replacing a formula its too slow | Excel Programming |