Workbook with big array functions slow to open first time in 2003

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have a workbook created in Excel 2000 that I am
beginning to use in 2003. The first time I opened the
book it took 2 hours, using 100% CPU. Once open I saved
to a new file and it now opens ok. I determined the slow
opening was caused by a group of array functions that
work on a table with 15,000 rows:

Sheet1
QueryTable results: 15,000 rows with about 15 columns of
data returned, then another 15 columns of simple
calculations done on each row.

Sheet2
40 cells containing array functions across the querytable
in sheet1. Array functions are like
{=sum(if(range1='w',if(range2='x',if(range3='y',if
(range4='z',range5)))))}

If I delete these array functions from the original sheet
(from Excel 2000) the workbook opens without delay in
2003.

Two questions:
1. Why is the workbook so slow to open the first time in
Office 2003?

2. Is it permanently fixed or will it need to do this
startup calculation again? Is it predictable? Can I turn
it off?

It seems like the first time 2003 encounters these big
array functions it decides to do some "big task".

Additional Test:
I created a new workbook in Excel 2003 that mirrors the
above calculations: recreated "sheet1" and "sheet2" from
scratch to eliminate the possibility of some leftover
garbage causing problems. The first time I saved the
workbook it took 2 hours, then once saved the first time
it opens/closes/calculates in a reasonable time (few
minutes to calculate).
Thanks for help, post back if this is not clear.

Terry
 
Hi
Excel 2003 recalculates the sheet completely if it was saved in an
older Excel version. So this should happen only the first time. After
saving the file in Excel 2003 it should open normally
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Array Function recalculate much slower in 2003 vs. 2000 1
replace all formulas with values in multiple worksheets 5
How to use vlookup in an array formula 2
Cell values to an array, then array transpose to another workbook 8
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Reset workbook back to Auto Calculate 8
Some users not able to open excel shared workbook 2
Problem with formula in grouped sheets 2

Back
Top