Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
Hi All,
I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
Some things to try a
Move conditions that are used in more than one array formulae out of the array formulae into helper columns. Sort the data and calculate start and end rows (or start rows and counts) for each subset block of data, then restrict your array formulae to the subsets. See if you can use pivot tables instead of your array functions. for more ideas see my website and in particular http://www.decisionmodels.com/optspeedj.htm regards Charles Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
Hi Don,
Thanks for your offer but I really want to be able to find it out myself. Wkr, JP "Don Guillett" wrote in message ... Smaller ranges to check?? If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "JP Ronse" wrote in message ... Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
Hi Charles,
Thanks for the tips. Found already some good hints. Wkr, JP "Charles Williams" wrote in message ... Some things to try a Move conditions that are used in more than one array formulae out of the array formulae into helper columns. Sort the data and calculate start and end rows (or start rows and counts) for each subset block of data, then restrict your array formulae to the subsets. See if you can use pivot tables instead of your array functions. for more ideas see my website and in particular http://www.decisionmodels.com/optspeedj.htm regards Charles Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
To be more concret.
One of functions I'm using is like this below: {=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$B$3)*(EventsHandled))} NCTime, EventHandler & EventsHandled refers to Events!A2:A20000, Events!B2:B20000, Events!C2:C20000 NCTime contains a date, EventsHandler a string and EventsHandled are figures. $B$1 is a year, e.g; 2009 D7..Dn: 1, 2, 3, ... 12 $B$3 like "JP" I defined the ranges big enough to be sure that I was not running out of the scope of the range when adding new data so that my functions will still return the correct values. At the end of the year, I found out that NCTime is about A2:A308. So, looking for a way to take only a valuable range in account. I've had a look on Chip Pearson's page about dynamic ranges but was not able to figure it out. Any help will be very appreciated. With kind regards, JP "Charles Williams" wrote in message ... Some things to try a Move conditions that are used in more than one array formulae out of the array formulae into helper columns. Sort the data and calculate start and end rows (or start rows and counts) for each subset block of data, then restrict your array formulae to the subsets. See if you can use pivot tables instead of your array functions. for more ideas see my website and in particular http://www.decisionmodels.com/optspeedj.htm regards Charles Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
How does the speed of that array formula compare with using SUMPRODUCT?
=SUMPRODUCT((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7) *(EventHandler=$B$3)*EventsHandled) or (perhaps quicker) =SUMPRODUCT(--(YEAR(NCTime)=$B$1),--(MONTH(NCTime)=D7),--(EventHandler=$B$3),*EventsHandled) -- David Biddulph "JP Ronse" wrote in message ... To be more concret. One of functions I'm using is like this below: {=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$B$3)*(EventsHandled))} NCTime, EventHandler & EventsHandled refers to Events!A2:A20000, Events!B2:B20000, Events!C2:C20000 NCTime contains a date, EventsHandler a string and EventsHandled are figures. $B$1 is a year, e.g; 2009 D7..Dn: 1, 2, 3, ... 12 $B$3 like "JP" I defined the ranges big enough to be sure that I was not running out of the scope of the range when adding new data so that my functions will still return the correct values. At the end of the year, I found out that NCTime is about A2:A308. So, looking for a way to take only a valuable range in account. I've had a look on Chip Pearson's page about dynamic ranges but was not able to figure it out. Any help will be very appreciated. With kind regards, JP "Charles Williams" wrote in message ... Some things to try a Move conditions that are used in more than one array formulae out of the array formulae into helper columns. Sort the data and calculate start and end rows (or start rows and counts) for each subset block of data, then restrict your array formulae to the subsets. See if you can use pivot tables instead of your array functions. for more ideas see my website and in particular http://www.decisionmodels.com/optspeedj.htm regards Charles Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
Sun, 28 Feb 2010 15:29:18 +0100 from JP Ronse :
- I have to use array functions because several conditions have to be checked You may or may not "have to use array functions". Consider SUMPRODUCT, which is much, much faster and can "check multiple conditions" just fine. http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
Then try what Charles (an expert in the field) and I said about smaller ranges to lookup. You can name blocks and use sumproduct for the named blocks. -- Don Guillett Microsoft MVP Excel SalesAid Software "JP Ronse" wrote in message ... Hi Don, Thanks for your offer but I really want to be able to find it out myself. Wkr, JP "Don Guillett" wrote in message ... Smaller ranges to check?? If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "JP Ronse" wrote in message ... Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
You can make it slightly faster by eliminating one array of tests:
(YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7) $B$1 is a year, e.g; 2009 D7..Dn: 1, 2, 3, ... 12 (TEXT(NCTime,"myyyy")=D7&B1) Using the double unary might also save a tick or two. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... How does the speed of that array formula compare with using SUMPRODUCT? =SUMPRODUCT((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7) *(EventHandler=$B$3)*EventsHandled) or (perhaps quicker) =SUMPRODUCT(--(YEAR(NCTime)=$B$1),--(MONTH(NCTime)=D7),--(EventHandler=$B$3),*EventsHandled) -- David Biddulph "JP Ronse" wrote in message ... To be more concret. One of functions I'm using is like this below: {=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$B$3)*(EventsHandled))} NCTime, EventHandler & EventsHandled refers to Events!A2:A20000, Events!B2:B20000, Events!C2:C20000 NCTime contains a date, EventsHandler a string and EventsHandled are figures. $B$1 is a year, e.g; 2009 D7..Dn: 1, 2, 3, ... 12 $B$3 like "JP" I defined the ranges big enough to be sure that I was not running out of the scope of the range when adding new data so that my functions will still return the correct values. At the end of the year, I found out that NCTime is about A2:A308. So, looking for a way to take only a valuable range in account. I've had a look on Chip Pearson's page about dynamic ranges but was not able to figure it out. Any help will be very appreciated. With kind regards, JP "Charles Williams" wrote in message ... Some things to try a Move conditions that are used in more than one array formulae out of the array formulae into helper columns. Sort the data and calculate start and end rows (or start rows and counts) for each subset block of data, then restrict your array formulae to the subsets. See if you can use pivot tables instead of your array functions. for more ideas see my website and in particular http://www.decisionmodels.com/optspeedj.htm regards Charles Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
You may or may not "have to use array functions".
SUMPRODUCT, which is much, much faster Not necessarily. Which of these do you think calculates faster: =SUMPRODUCT(--(A1:A20000=1),--(B1:B20000=2),--(C1:C20000=3),D1:D20000) Array entered: =SUM(IF(A1:A20000=1,IF(B1:B20000=2,IF(C1:C20000=3, D1:D20000)))) You're right if you said the SUM(IF array. -- Biff Microsoft Excel MVP "Stan Brown" wrote in message t... Sun, 28 Feb 2010 15:29:18 +0100 from JP Ronse : - I have to use array functions because several conditions have to be checked You may or may not "have to use array functions". Consider SUMPRODUCT, which is much, much faster and can "check multiple conditions" just fine. http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Speeding up Excel calculations
Hi All,
You gave a lot of useful suggestions. I'll try them. Thanks a mot. With kind regards, JP "T. Valko" wrote in message ... You can make it slightly faster by eliminating one array of tests: (YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7) $B$1 is a year, e.g; 2009 D7..Dn: 1, 2, 3, ... 12 (TEXT(NCTime,"myyyy")=D7&B1) Using the double unary might also save a tick or two. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... How does the speed of that array formula compare with using SUMPRODUCT? =SUMPRODUCT((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7) *(EventHandler=$B$3)*EventsHandled) or (perhaps quicker) =SUMPRODUCT(--(YEAR(NCTime)=$B$1),--(MONTH(NCTime)=D7),--(EventHandler=$B$3),*EventsHandled) -- David Biddulph "JP Ronse" wrote in message ... To be more concret. One of functions I'm using is like this below: {=SUM((YEAR(NCTime)=$B$1)*(MONTH(NCTime)=D7)*(Even tHandler=$B$3)*(EventsHandled))} NCTime, EventHandler & EventsHandled refers to Events!A2:A20000, Events!B2:B20000, Events!C2:C20000 NCTime contains a date, EventsHandler a string and EventsHandled are figures. $B$1 is a year, e.g; 2009 D7..Dn: 1, 2, 3, ... 12 $B$3 like "JP" I defined the ranges big enough to be sure that I was not running out of the scope of the range when adding new data so that my functions will still return the correct values. At the end of the year, I found out that NCTime is about A2:A308. So, looking for a way to take only a valuable range in account. I've had a look on Chip Pearson's page about dynamic ranges but was not able to figure it out. Any help will be very appreciated. With kind regards, JP "Charles Williams" wrote in message ... Some things to try a Move conditions that are used in more than one array formulae out of the array formulae into helper columns. Sort the data and calculate start and end rows (or start rows and counts) for each subset block of data, then restrict your array formulae to the subsets. See if you can use pivot tables instead of your array functions. for more ideas see my website and in particular http://www.decisionmodels.com/optspeedj.htm regards Charles Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speeding up Excel 2003 | Excel Discussion (Misc queries) | |||
Speeding up calculations | Excel Discussion (Misc queries) | |||
Speeding Up A Spreadsheet | Excel Discussion (Misc queries) | |||
Speeding up workbook calculations | Excel Discussion (Misc queries) | |||
Speeding Saving Process | Excel Discussion (Misc queries) |