Re: SLOW Response on EXCEL 2003

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"EricB" <EricB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7A88FF0B-B4B8-4096-9D51-E46FFB126ED8@xxxxxxxxxxxxxxxx
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

"Charles Williams" wrote:

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" <EricB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7F442C4F-2FB0-4567-9DEF-135AF3CDD5A1@xxxxxxxxxxxxxxxx
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" wrote:

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)


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

"EricB" <EricB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:48E60644-2488-4ED2-BDE2-A0EE1CA82CF8@xxxxxxxxxxxxxxxx
http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-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 *** 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










.

Quantcast