SLOW Response on EXCEL 2003

[EricB]

http://blogs.msdn.com/excel/archive...stest-way-to-scan-a-large-range-in-excel.aspx

The above refers but point to Excel 2007 with Macro usage.

I'm not a MACRO user, I just stick to basic formulas (Count, Countif,
Sumproduct).

Workbook consists of charts, Data pasted (25000 kb, 1.5 million cells in
..csv format) , master sheet containing most of my formulas (200 rows vs 13
columns).
When pasting my Data (.csv) to my Excel 2003 workbook I experience i
terrible delay in data updating, some 15 to 20 minutes at time.

1) Are there clashes between .csv & excel 2003 workbooks
2) What is the ideal Performance settings for an "Intel Centrino Duo"
proccessor?

I have cleaned the system (deleted temp folder, Defraged, etc.)

Any help will be appreciated

EricB

 

[Charles Williams]

Presumably you have already switched to Manual calculation mode. But be
aware that just opening a .csv file with Excel triggers a recalculation of
all open workbooks even in manual mode, so it may be worthwhile opening the
..csv file first, then opening the workbook containing the formulae.

The culprit is probably your SUMPRODUCT formulas. You need to find a way of
speeding these up, some suggestions are:

- Use pivottables and charts instead
- reduce the range of data that each SUMPRODUCT references
- use helper columns to reduce the number of arguments in SUMPRODUCT
- Sort the data so that your formulas only need to reference small blocks of
adjacent rows
- use an array formula UDF instead of SUMPRODUCT#
- switch to Excel 2007 and use SUMIFS, COUNTIFS etc (but your Charts will be
a lot slower)

 

[EricB]

Hi Charles

1) I am not using Manual Calculation Mode - I run on Auto - Is it
recommended that I use Manual Calculation?
2) Sumproduct Ranges are vast - please elaborate on "use an array formula
UDF instead of SUMPRODUCT#"
3) I work for a Corporate that is sticky when it comes to upgrading
programs. I had the opportunity to work a little on EXCEL2007 which looks
like a fairly smooth program, I have however not run my data on EXCEL2007
(Yet)

Regards

Eric

 

[Charles Williams]

I would switch to Manual and use F9 to calculate, but i suspect it will
still be far too slow.

An array formula UDF is a VBA User defined Function that returns an array of
values to the range it is called from. It has to be entered into the range
with Control Shift Enter.
You would need to write the UDF: if you are not already reasonably familiar
with VBA I would not recommend attempting this.

I think the suggestions I listed are roughly in sequence from simplest to
hardest ...

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[EricB]

We are getting to technical now: UDF/VBA will have to be studied to try and
understand where we are going to: I forwarded an email to you, via WEB Link.

Manual/Automatic Calculation makes no difference to speed.

Being in South Africa - The FastEXcel package at USD44 will relate to quite
an amount for my pocket. Just not worth going there 'for me'.

Regards

eric

 

[Shane Devenshire]

Hi,

UDF - user defined function - you program a function that Excel doesn't have
VBA - Visual Basic for Applications - Excel's programming language

Cheers,
Shane Devenshire

 

[Charles Williams]

I suggest you post the SUMPRODUCT formula you are using and some details of
the data it is accessing to see if anyone can suggest improvements.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com