Slow vlookups and start time

[livid7]

Hello,

We are experiencing slow vlookups on files larger then 1mb (5+ minutes)
and also it takes longer then it should to start and close excel.

Also, I was wondering about running office updates on just an excel
install. Can you do this? Will it just install the components it
needs?

Thanks,
Alex

 

[Bob Greenblatt]

Hello,

We are experiencing slow vlookups on files larger then 1mb (5+ minutes)
and also it takes longer then it should to start and close excel.

Also, I was wondering about running office updates on just an excel
install. Can you do this? Will it just install the components it
needs?

Thanks,
Alex

You should be able to run Office Update on just an Excel installation.
Update will only update the components that it needs.

Regarding vlookups - does it take 5 minutes for each, or several? His there
a difference with Calculation set to manual? When you press control-end, is
the cell selected the "real" last cell in the workbook, or is it much
further down and to the right? How big is the table that vlookup references?
Can you describe the environment in more detail, such as Excel version, OS
version, Vlookup table parameters, how many vlookups are being done, etc.

With this information we can get closer to solving this for you.

 

[livid7]

Hi Bob,

Thanks for the response. We are using osx 10.3.9 and excel 2004.

We just upgraded to 2004 and were using v.X before. It seems like
everything is taking longer in 2004, but here are the specifics to what
you asked.

Regarding vlookups - does it take 5 minutes for each, or several?
There are many vlookups taking place. I have many rows of a table that
are populated by lookup to another file. I work with both files open.
Whenever the destination file recalculates, it takes several minutes.

His there a difference with Calculation set to manual?
I can see no difference in calculation time between automatic and
manual.

A control-end command does land me about 40% further down, but no
further right. I'm not sure why it falls there. There is no table
reference that I can find that extends that far beyond the data.

How big is the table that vlookup references?

The tables are various sizes, anywhere from fewer than 10 rows to 1500+
rows. Typically I am returning the second column of a two column
table, but some files contain and use 10-15 columns from which I pull
data.

Another thing that may dramatically increase calculation time is the
fact that many of the lookups are within "if" statements for error
management.

Thanks for the help!

-Alex

 

[Bob Greenblatt]

Hi Bob,

Thanks for the response. We are using osx 10.3.9 and excel 2004.

We just upgraded to 2004 and were using v.X before. It seems like
everything is taking longer in 2004, but here are the specifics to what
you asked.

Regarding vlookups - does it take 5 minutes for each, or several?
There are many vlookups taking place. I have many rows of a table that
are populated by lookup to another file. I work with both files open.
Whenever the destination file recalculates, it takes several minutes.

His there a difference with Calculation set to manual?
I can see no difference in calculation time between automatic and
manual.

A control-end command does land me about 40% further down, but no
further right. I'm not sure why it falls there. There is no table
reference that I can find that extends that far beyond the data.

How big is the table that vlookup references?

The tables are various sizes, anywhere from fewer than 10 rows to 1500+
rows. Typically I am returning the second column of a two column
table, but some files contain and use 10-15 columns from which I pull
data.

Another thing that may dramatically increase calculation time is the
fact that many of the lookups are within "if" statements for error
management.

Thanks for the help!

-Alex

Well, it seems like you just have a very large file with a lot of
calculations. Things do take somewhat longer with Excel 2004.

If your vlookup is within an IF statement, like:
if(iserror(vlookup)),xx,vlookup)) then you are doing 2 lookups for each
cell. There may be a better way to detect errors, like conditional
formatting, or format the error condition as white text on a white
background. This will reduce the number of calculations.

 

[livid7]

Yes, Excel 2004 seems much slower then version X. We are running it on
a dual G5 with 2gb of ram. I am just really surprised it isn't faster
then it is. Do you have any general things you do to increase the
performance of the app?

Thanks,

Alex

 

[Bob Greenblatt]

Yes, Excel 2004 seems much slower then version X. We are running it on
a dual G5 with 2gb of ram. I am just really surprised it isn't faster
then it is. Do you have any general things you do to increase the
performance of the app?

Thanks,

Alex

Well, there are many things that might be done depending on what your
workbook(s) are doing and how they are designed. Can you pin down what is
slow- macro execution, recalculation, both? Can you isolate it?

As I pointed out in a prior message, perhaps a different method of error
handling will avoid the double lookups that you now seem to be doing.

 

[Mike Middleton]

Alex -

Here's one of the standard information resources:

http://www.decisionmodels.com/calcsecrets.htm

- Mike
www.mikemiddleton.com

Yes, Excel 2004 seems much slower then version X. We are running it on a
dual G5 with 2gb of ram. I am just really surprised it isn't faster then it
is. Do you have any general things you do to increase the performance of
the app?
Thanks,
Alex

 

[livid7]

Thanks for the help Bob and Mike! It is much appreciated.

I think we are going to live with the slowness for now. We are in the
long process of moving as far away from excel as possible, but it seems
like it will be a necessity for a long time to come. Excel 2004 offers
more stability then v.X but we have had to give up speed to get that; I
guess you can't ask for too much. Hopefully (and it sounds like it
will happen) MS will release a new version of Excel that is faster and
more stable then the current offering for the Mac.

-alex