Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb,
with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® Core„¢2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
You will probably get some improvement from a Quad core chip running at the
same speed as a Duo (maybe 20-25 mins instead of 30?) The improvement from multiple processors is very problem dependent, but you could get an idea by seeing what the difference is between calculating using only 1 core and calculating using 2 cores on your current system. (Office Button--Excel Options--Advanced--Scoll down to Formulas section--uncheck Enable multi-threaded calculation). You could probably fairly easily get a much larger improvement by taking some of the conditions out of your SUMPRODUCT formulae and putting them in helper columns, or by sorting your data and doing the SUMPRODUCT calculations on a sorted subset of data. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Mike" wrote in message ... I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® CoreT2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
Formulas especialy SUMPRODUCT are slow. Replacing your formulas with macros
will speed up the calculation time. You ca add a control button to your workbook to perform the calculations rather than to manually perform updates. "Mike" wrote: I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® Core„¢2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
I can't advise on the hardware route, but it strikes me that to
improve the calculation speed you need to look at your Excel file and see how this can be re-designed to speed things up. If you have a lot of SP formulae (especially covering large arrays) these will slow things down considerably, but can sometimes be replaced with SUMIF or COUNTIF formulae (with the use of helper columns) which run much more quickly. There are many other techniques shown on this site: http://www.decisionmodels.com/optspeedb.htm and the SUMIF example shows how a calculation taking over 750 seconds can be brought down to under 1 sec. Hope this helps. Pete On Feb 3, 10:41*am, Mike wrote: I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read *a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® Core™2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
to Joel, Charles and Pete - many thanks.
I love sumproduct but this new exercise has caught me out. I will try the sumif's first. I was looking at the decisonmodels.com page and hapy to spend the money for the fastExcel - if it works. It says it works on MS Office 07 but doesn't state it works on Vista. Anyone have any experienec with FastExcel on Vista? Mike "Mike" wrote: I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® Core„¢2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
If you can replace your SUMPRODUCT formulae with Excel 2007 SUMIFS that will
be significantly faster (but don't forget that SUMIFS is not available in earlier Excel versions). But its still worth looking at helper columns etc. There are FastExcel users who are happily using FastExcel under Vista. FastExcel will help you prioritise the calculation bottlenecks, and will speed up VBA UDFs if you have lots of them, and has a faster VLOOKUP function, but does not currently directly offer a faster SUMPRODUCT function. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Mike" wrote in message ... to Joel, Charles and Pete - many thanks. I love sumproduct but this new exercise has caught me out. I will try the sumif's first. I was looking at the decisonmodels.com page and hapy to spend the money for the fastExcel - if it works. It says it works on MS Office 07 but doesn't state it works on Vista. Anyone have any experienec with FastExcel on Vista? Mike "Mike" wrote: I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® CoreT2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
Charles,
Thanks again. I have thousands of Vlookup and Hlookups so I will give FastExcel a shot. I have sent an email direct to them to ask about Vista as there web site lists all MS systems except Vista - looks like an oversight on their part. I haven't used Sumifs as I naturally migrated from MS 2003 but now on MS 2007 and have been using Countifs. Really appreciate your advice. Mike (Melbourne AU) "Charles Williams" wrote: If you can replace your SUMPRODUCT formulae with Excel 2007 SUMIFS that will be significantly faster (but don't forget that SUMIFS is not available in earlier Excel versions). But its still worth looking at helper columns etc. There are FastExcel users who are happily using FastExcel under Vista. FastExcel will help you prioritise the calculation bottlenecks, and will speed up VBA UDFs if you have lots of them, and has a faster VLOOKUP function, but does not currently directly offer a faster SUMPRODUCT function. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Mike" wrote in message ... to Joel, Charles and Pete - many thanks. I love sumproduct but this new exercise has caught me out. I will try the sumif's first. I was looking at the decisonmodels.com page and hapy to spend the money for the fastExcel - if it works. It says it works on MS Office 07 but doesn't state it works on Vista. Anyone have any experienec with FastExcel on Vista? Mike "Mike" wrote: I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® CoreT2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
Mike, the slow performance is a "feature" of Excel 2007 and workbooks with
an extremely large number of formulas. There is some hope that Microsoft will fix with Excel 2009 or with a new SP release for 2007. Don't bet a lot. The best solution is to eliminate formulas you don't need. If you have gotten the results you need via your lookups and the data is static, convert the lookups formulas to values by Copy, Paste Special Values. The same goes for other formulas. Robert Flanagan http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Mike" wrote in message ... I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® CoreT2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
Mike, unless I'm totally mistaken, Charles Williams "IS" DecisionModels so I
think you've already gotten the answer from the source. The advice on the site is sound, well researched by them, and some great ideas there for improving Excel performance. I have FastExcel running on a Vista system here and have taken the liberty of using it both in Excel 2007 on that system and within a virtual machine on it that runs Windows XP and Excel 2003. Works just fine. I also agree with what's already been said about substituting SUMIFS for your SUMPRODUCT formulas where possible. It is one big improvement within Excel 2007. "Mike" wrote: Charles, Thanks again. I have thousands of Vlookup and Hlookups so I will give FastExcel a shot. I have sent an email direct to them to ask about Vista as there web site lists all MS systems except Vista - looks like an oversight on their part. I haven't used Sumifs as I naturally migrated from MS 2003 but now on MS 2007 and have been using Countifs. Really appreciate your advice. Mike (Melbourne AU) "Charles Williams" wrote: If you can replace your SUMPRODUCT formulae with Excel 2007 SUMIFS that will be significantly faster (but don't forget that SUMIFS is not available in earlier Excel versions). But its still worth looking at helper columns etc. There are FastExcel users who are happily using FastExcel under Vista. FastExcel will help you prioritise the calculation bottlenecks, and will speed up VBA UDFs if you have lots of them, and has a faster VLOOKUP function, but does not currently directly offer a faster SUMPRODUCT function. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Mike" wrote in message ... to Joel, Charles and Pete - many thanks. I love sumproduct but this new exercise has caught me out. I will try the sumif's first. I was looking at the decisonmodels.com page and hapy to spend the money for the fastExcel - if it works. It says it works on MS Office 07 but doesn't state it works on Vista. Anyone have any experienec with FastExcel on Vista? Mike "Mike" wrote: I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® CoreT2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
Bob and JLatham,
Thanks for the notes. I will start removing sumproducts with sumifs and I like Bob's note. "Bob Flanagan" wrote: Mike, the slow performance is a "feature" of Excel 2007 and workbooks with an extremely large number of formulas. There is some hope that Microsoft will fix with Excel 2009 or with a new SP release for 2007. Don't bet a lot. The best solution is to eliminate formulas you don't need. If you have gotten the results you need via your lookups and the data is static, convert the lookups formulas to values by Copy, Paste Special Values. The same goes for other formulas. Robert Flanagan http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Mike" wrote in message ... I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® CoreT2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel - the need for speed
Just for example: Formula =sumproduct(a1:a10,b1:b10) can be replaced by:
=sum(a1:a10*b1:b10) then press ctrl+shift+enter (special shortcut to enter array formula, which must be always followed by this shortcut, otherwice they return ERROR), not sure it is faster for computing and less memory consuming, you have to make this homework, but I fell in love with array formulas and almost completely got rid of sumproducts, sumifs, countifs etc. and strongly recommend to use arrays "Mike" wrote: I use Office 2007 and a heavy user of Excel. My latest spreadsheet is 30mb, with approx 1 million formulas. There are thousands of sumproducts. I will be duplicatin these spreadsheets for numerous locations. It is now taking 30 minutes to calculate (I have set it to manual calc). I have a new Lenovo T61 with 2.0 gb RAM and Intel Core Duo 2 CPU @2.2ghz. I read a previuos post to go into arrays UDF and VBA but I dont have the time to learn that part fo the process. Do you have experience on if I go to a new notebook with the Intel® Core„¢2 Quad mobile processor chip. It is quite expensive but i need faster processing. I don't want to spend the money if the impact is marginal. Thanks in advance for any advice. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Charting speed | Charts and Charting in Excel | |||
Excel Speed Performance help | Excel Discussion (Misc queries) | |||
Speed of Excel | Excel Discussion (Misc queries) | |||
Speed and Excel Version | Excel Discussion (Misc queries) | |||
Can you speed UP drag speed? | Excel Discussion (Misc queries) |