Extremely slow calculation of formulas

[Rolf]

Excel 2003/SP2, running under Windows XP takes forever to calculate a
formula when it is copied to a new cell. My computer is a P4/1.3Gig with 768MB
memory.

The problem occurred within the last day or so, and I can not figure out a
possible cause. Rebooting and running the error correction program for the
office installation had no effect. When pasting, the CPU usage jumps to 100%,
the task manager shows several instances of Excel as running. Have I
exceeded the programs capability with this Lookup formula?

The formula:

[=IF(J3763="","",
IF(J3763<$A$1,VLOOKUP(A3763,Data1,HLOOKUP(J3763,Data1,2),FALSE),
IF(J3763<$A$2,VLOOKUP(A3763,Data2,HLOOKUP(J3763,Data2,2),FALSE),
IF(J3763<$A$3,VLOOKUP(A3763,Data3,HLOOKUP(J3763,Data3,2),FALSE),
VLOOKUP(A3763,Data4,HLOOKUP(J3763,Data4,2))))))]

is in a worksheet with data in cells A1 to K4500, extracts the price for the
stock symbol in col A for the date entered in column J.

Column J does not contain any dates, except when manually entered.
Data1, 2, 3 and 4 are defined names for cell blocks in four worksheets of an
external workbook.
Cells A1 to A4 contain reference dates for the last record day contained in
the
referenced worksheet.

The workbook has 5 worksheets,
Worksheet A has 4 columns of formulas copied into 4500 rows.
Worksheet B has 2 columns of formulas copied into 600 rows
Worksheet C has 2 columns of formulas copied into 40 rows
The other worksheets contain only data.

Any help will be greatly appreciated
Rolf

PS. Posted originally under General Questions

 

[ker_01]

First of all, while your formula does not hit any Excel limitation I know of
(such as 7 levels of embedded conditions), vlookup and hlookup are fairly
intensive formulas. Do you have this in only one cell, or is this in every
cell in that column (e.g. the 4500 rows)? If you have this formula in 4500
cells, then I'm not surprised that this would take a long time to calculate.

The extra instances of Excel may be Excel opening the source named range
workbooks, although I can't be sure. That will add some overhead, probably
not that much but it may be worth testing. Pull a copy of each of those
source worksheets into your main workbook, adjust your named ranges to point
within that same workbook, and see if that is any faster.

Is your main workbook and named range workbooks all local, or are you
accessing over a network?

Another thing (at least with Excel2007, I don't know about 2003) is that
clicking in the workbook (changing the focus, or selecting another cell)
seems to reset the calculations, and you have to wait all over again (you may
already be aware, I'm just writing down anything I can think of)

Overall though, I think the basic issue is the number of vlookups and
hlookups you are doing (2 per calculation/per cell). You could always try a
combination of MATCH and INDEX to replace the V and H-Lookups, but I don't
know if that would be any faster...

Are your source ranges sorted? I would suspect that having the source ranges
sorted in ascending order may speed up the lookups, even if you are using
FALSE to ensure an exact match (I'm making some assumptions about what search
algorithm is used by Excel)

My last suggestion is to consider a VBA (programming) solution; depending on
your data format, there may be a way to speed up the processing- and because
the VBA would only run when prompted, you would not have to worry about Excel
locking up to recalculate everything when you won't want it to (if you change
a cell value that doesn't affect your lookups, or if you want to enter a
bunch of values and not have Excel recalculate in between each entry)

Without knowing your data set I won't make assumptions; I notice that your
HLookup does not include the final optional parameter for exact match.

HTH,
Keith

Excel 2003/SP2, running under Windows XP takes forever to calculate a
formula when it is copied to a new cell. My computer is a P4/1.3Gig with 768MB
memory.

The problem occurred within the last day or so, and I can not figure out a
possible cause. Rebooting and running the error correction program for the
office installation had no effect. When pasting, the CPU usage jumps to 100%,
the task manager shows several instances of Excel as running. Have I
exceeded the programs capability with this Lookup formula?

The formula:

[=IF(J3763="","",
IF(J3763<$A$1,VLOOKUP(A3763,Data1,HLOOKUP(J3763,Data1,2),FALSE),
IF(J3763<$A$2,VLOOKUP(A3763,Data2,HLOOKUP(J3763,Data2,2),FALSE),
IF(J3763<$A$3,VLOOKUP(A3763,Data3,HLOOKUP(J3763,Data3,2),FALSE),
VLOOKUP(A3763,Data4,HLOOKUP(J3763,Data4,2))))))]

is in a worksheet with data in cells A1 to K4500, extracts the price for the
stock symbol in col A for the date entered in column J.

Column J does not contain any dates, except when manually entered.
Data1, 2, 3 and 4 are defined names for cell blocks in four worksheets of an
external workbook.
Cells A1 to A4 contain reference dates for the last record day contained in
the
referenced worksheet.

The workbook has 5 worksheets,
Worksheet A has 4 columns of formulas copied into 4500 rows.
Worksheet B has 2 columns of formulas copied into 600 rows
Worksheet C has 2 columns of formulas copied into 40 rows
The other worksheets contain only data.

Any help will be greatly appreciated
Rolf

PS. Posted originally under General Questions

 

[Rolf]

Keith, thanks for the reply and helpful suggestions.
The Worksheet is used to back test recommendations/opinions of a technical
advisory service for stocks, and is updated weekly.

In answer to your questions and comments:

The missing exact match parameter for the final calculation is an oversight.

Each of the 4500 rows in column J contain the quoted formula.
The referenced data in the external workbook are sorted alphabetically in
the first column and by date in the first row.
The defined names, Data1 etc. refer to a data block C9:IV300 of the external
worksheets.

In addition to the formulas in column J, each of the 4500 rows in column G
contain simple VLookup formulas referencing a different workbook. Columns H
and I have computation formulas.

I too, thought that the hangup may be the lookup formulas, but could not
understand the delay caused by copying, since the first argument in the
formula [=IF(J3763="","",] is a condition that ends any further calculation
if the referenced cell does not contain an entry.
Since the dates are manually entered in column "J" and deleted when the
desired data is extracted, Excel should not have to look in the referenced
workbooks.

My confusion is the delay when copying the formula into newly added rows,
normally about 100, but also when opening the workbook and selecting the
update option.

The calculation, once the formulas are extended to new rows is instantaneous
when a date is entered into cell in Column J.

Thanks again for your reply. I'll try your suggestions using the MATCH and
INDEX functions. I am not familiar with VBA programming, but will make a
renewed effort learn it. I switched from Quattro Pro, where I was able to
make extensive use of macros, to Excel but had difficulties understanding the
concept,or find information on the language.

Rolf

First of all, while your formula does not hit any Excel limitation I know of
(such as 7 levels of embedded conditions), vlookup and hlookup are fairly
intensive formulas. Do you have this in only one cell, or is this in every
cell in that column (e.g. the 4500 rows)? If you have this formula in 4500
cells, then I'm not surprised that this would take a long time to calculate.

The extra instances of Excel may be Excel opening the source named range
workbooks, although I can't be sure. That will add some overhead, probably
not that much but it may be worth testing. Pull a copy of each of those
source worksheets into your main workbook, adjust your named ranges to point
within that same workbook, and see if that is any faster.

Is your main workbook and named range workbooks all local, or are you
accessing over a network?

Another thing (at least with Excel2007, I don't know about 2003) is that
clicking in the workbook (changing the focus, or selecting another cell)
seems to reset the calculations, and you have to wait all over again (you may
already be aware, I'm just writing down anything I can think of)

Overall though, I think the basic issue is the number of vlookups and
hlookups you are doing (2 per calculation/per cell). You could always try a
combination of MATCH and INDEX to replace the V and H-Lookups, but I don't
know if that would be any faster...

Are your source ranges sorted? I would suspect that having the source ranges
sorted in ascending order may speed up the lookups, even if you are using
FALSE to ensure an exact match (I'm making some assumptions about what search
algorithm is used by Excel)

My last suggestion is to consider a VBA (programming) solution; depending on
your data format, there may be a way to speed up the processing- and because
the VBA would only run when prompted, you would not have to worry about Excel
locking up to recalculate everything when you won't want it to (if you change
a cell value that doesn't affect your lookups, or if you want to enter a
bunch of values and not have Excel recalculate in between each entry)

Without knowing your data set I won't make assumptions; I notice that your
HLookup does not include the final optional parameter for exact match.

HTH,
Keith

Excel 2003/SP2, running under Windows XP takes forever to calculate a
formula when it is copied to a new cell. My computer is a P4/1.3Gig with 768MB
memory.

The problem occurred within the last day or so, and I can not figure out a
possible cause. Rebooting and running the error correction program for the
office installation had no effect. When pasting, the CPU usage jumps to 100%,
the task manager shows several instances of Excel as running. Have I
exceeded the programs capability with this Lookup formula?

The formula:

[=IF(J3763="","",
IF(J3763<$A$1,VLOOKUP(A3763,Data1,HLOOKUP(J3763,Data1,2),FALSE),
IF(J3763<$A$2,VLOOKUP(A3763,Data2,HLOOKUP(J3763,Data2,2),FALSE),
IF(J3763<$A$3,VLOOKUP(A3763,Data3,HLOOKUP(J3763,Data3,2),FALSE),
VLOOKUP(A3763,Data4,HLOOKUP(J3763,Data4,2))))))]

is in a worksheet with data in cells A1 to K4500, extracts the price for the
stock symbol in col A for the date entered in column J.

Column J does not contain any dates, except when manually entered.
Data1, 2, 3 and 4 are defined names for cell blocks in four worksheets of an
external workbook.
Cells A1 to A4 contain reference dates for the last record day contained in
the
referenced worksheet.

The workbook has 5 worksheets,
Worksheet A has 4 columns of formulas copied into 4500 rows.
Worksheet B has 2 columns of formulas copied into 600 rows
Worksheet C has 2 columns of formulas copied into 40 rows
The other worksheets contain only data.

Any help will be greatly appreciated
Rolf

PS. Posted originally under General Questions

 

[ker_01]

Hi Rolf-

If the delay is only when copying the new formulas (and not when changing
individual values) then I wouldn't worry too much about the Match/Index or
VBA solutions. It sounds more like Excel is doing something(?) in the
background that is doesn't have to repeat on an ongoing basis.

Here is one more thing to try; go into the Excel settings, and turn off
calculation (e.g. set to manual calc instead of auto calc). Copy the formula
to all the cells. It /should/ copy quickly without any extra delay. Then turn
calculation back on, and if it doesn't calculate on it's own, press F9 to
force an updated calculation. I'd be curious as to whether that initial
calculation takes just as long as when you copy/paste, or if it is
significantly faster.

If (with calculation turned off) it still takes a long time to paste the
formula down, then I'd suspect it is some inefficient process excel is using
as part of the copy/paste process. If this occurs, then what specific process
do you use to copy?
Do you:
(a) copy one cell, select the destination columns, and paste
(b) select the cell with the formula, and double-click the cell handle
(c) select the cell with the formula, and click/drag the cell handle down
your range
(d) something else?

HTH,
Keith

Keith, thanks for the reply and helpful suggestions.
The Worksheet is used to back test recommendations/opinions of a technical
advisory service for stocks, and is updated weekly.

In answer to your questions and comments:

The missing exact match parameter for the final calculation is an oversight.

Each of the 4500 rows in column J contain the quoted formula.
The referenced data in the external workbook are sorted alphabetically in
the first column and by date in the first row.
The defined names, Data1 etc. refer to a data block C9:IV300 of the external
worksheets.

In addition to the formulas in column J, each of the 4500 rows in column G
contain simple VLookup formulas referencing a different workbook. Columns H
and I have computation formulas.

I too, thought that the hangup may be the lookup formulas, but could not
understand the delay caused by copying, since the first argument in the
formula [=IF(J3763="","",] is a condition that ends any further calculation
if the referenced cell does not contain an entry.
Since the dates are manually entered in column "J" and deleted when the
desired data is extracted, Excel should not have to look in the referenced
workbooks.

My confusion is the delay when copying the formula into newly added rows,
normally about 100, but also when opening the workbook and selecting the
update option.

The calculation, once the formulas are extended to new rows is instantaneous
when a date is entered into cell in Column J.

Thanks again for your reply. I'll try your suggestions using the MATCH and
INDEX functions. I am not familiar with VBA programming, but will make a
renewed effort learn it. I switched from Quattro Pro, where I was able to
make extensive use of macros, to Excel but had difficulties understanding the
concept,or find information on the language.

Rolf

First of all, while your formula does not hit any Excel limitation I know of
(such as 7 levels of embedded conditions), vlookup and hlookup are fairly
intensive formulas. Do you have this in only one cell, or is this in every
cell in that column (e.g. the 4500 rows)? If you have this formula in 4500
cells, then I'm not surprised that this would take a long time to calculate.

The extra instances of Excel may be Excel opening the source named range
workbooks, although I can't be sure. That will add some overhead, probably
not that much but it may be worth testing. Pull a copy of each of those
source worksheets into your main workbook, adjust your named ranges to point
within that same workbook, and see if that is any faster.

Is your main workbook and named range workbooks all local, or are you
accessing over a network?

Another thing (at least with Excel2007, I don't know about 2003) is that
clicking in the workbook (changing the focus, or selecting another cell)
seems to reset the calculations, and you have to wait all over again (you may
already be aware, I'm just writing down anything I can think of)

Overall though, I think the basic issue is the number of vlookups and
hlookups you are doing (2 per calculation/per cell). You could always try a
combination of MATCH and INDEX to replace the V and H-Lookups, but I don't
know if that would be any faster...

Are your source ranges sorted? I would suspect that having the source ranges
sorted in ascending order may speed up the lookups, even if you are using
FALSE to ensure an exact match (I'm making some assumptions about what search
algorithm is used by Excel)

My last suggestion is to consider a VBA (programming) solution; depending on
your data format, there may be a way to speed up the processing- and because
the VBA would only run when prompted, you would not have to worry about Excel
locking up to recalculate everything when you won't want it to (if you change
a cell value that doesn't affect your lookups, or if you want to enter a
bunch of values and not have Excel recalculate in between each entry)

Without knowing your data set I won't make assumptions; I notice that your
HLookup does not include the final optional parameter for exact match.

HTH,
Keith

Excel 2003/SP2, running under Windows XP takes forever to calculate a
formula when it is copied to a new cell. My computer is a P4/1.3Gig with 768MB
memory.

The problem occurred within the last day or so, and I can not figure out a
possible cause. Rebooting and running the error correction program for the
office installation had no effect. When pasting, the CPU usage jumps to 100%,
the task manager shows several instances of Excel as running. Have I
exceeded the programs capability with this Lookup formula?

The formula:

[=IF(J3763="","",
IF(J3763<$A$1,VLOOKUP(A3763,Data1,HLOOKUP(J3763,Data1,2),FALSE),
IF(J3763<$A$2,VLOOKUP(A3763,Data2,HLOOKUP(J3763,Data2,2),FALSE),
IF(J3763<$A$3,VLOOKUP(A3763,Data3,HLOOKUP(J3763,Data3,2),FALSE),
VLOOKUP(A3763,Data4,HLOOKUP(J3763,Data4,2))))))]

is in a worksheet with data in cells A1 to K4500, extracts the price for the
stock symbol in col A for the date entered in column J.

Column J does not contain any dates, except when manually entered.
Data1, 2, 3 and 4 are defined names for cell blocks in four worksheets of an
external workbook.
Cells A1 to A4 contain reference dates for the last record day contained in
the
referenced worksheet.

The workbook has 5 worksheets,
Worksheet A has 4 columns of formulas copied into 4500 rows.
Worksheet B has 2 columns of formulas copied into 600 rows
Worksheet C has 2 columns of formulas copied into 40 rows
The other worksheets contain only data.

Any help will be greatly appreciated
Rolf

PS. Posted originally under General Questions

 

[Rolf]

Hi Keith,

Tried your suggestions, but changing the calculation method had no effect.
The different copy paste methods produced the same result.

I must be afflicted with an early onset of Alzheimer's or have contracted a
case of Mad Cow, akin Denny Crane (Boston Legal), but I found the answer in
one of your earlier posts.

With the source spreadsheet open, the copy / paste process is flawless, with
it closed causes the delay. At least, this gives me a workaround till I find
a better solution to the problem.

Keith, thanks again for your help and patience in solving this quandary for
me.

Rolf