excel - the need for speed

M

Mike

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
 
C

Charles Williams

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
 
J

Joel

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.
 
P

Pete_UK

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
 
M

Mike

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
 
C

Charles Williams

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
 
M

Mike

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

Bob Flanagan

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
 
J

JLatham

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.
 
M

Mike

Bob and JLatham,

Thanks for the notes. I will start removing sumproducts with sumifs and I
like Bob's note.
 
A

Alojz

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
 
Top