Calculation slowdown at the bottom of worksheet

[rcl2884]

Did microsoft not expect anyone to use the bottom rows of a
spreadsheet? I was given a file with about 120,000 records. Normally I
would load this into Access for analysis, but I was giving this data
to someone not familiar with Access and so I loaded them into Excel
2003.

There were only four columns of data and I added a fifth column that
included a very simply calculation. Since I am limited by 64K rows, I
pasted the rows into two adjacent sets of columns with about 60,000
each.

The problem... recalculation takes forever. (20 minutes).

I have run into this problem before and suspect that things bog down
if you use more than 32K rows. (something to do with memory?)

I pasted the data into four sets of adjacent columns and things were
much better. Here are some specific times. (Nothing else running on
my computer)

Recalc with 2 sets of 60K rows: 1240 seconds
Recalc with 4 sets of 30K rows: 0.3 seconds
VBA macro that does the calc on cell at a time: 9 seconds

What gives? Does anyone have an explantion? Are there any way around
this log jam, if you actually want to use 60K rows? What happens on
Excel 2007 with 1,000K rows?

....Rick

 

[Charles Williams]

Hi Rick,

AFAIK there is nothing in Excel's calculation engine that slows down beyond
32000 rows,
although the Google office desktop com addin does tend to slow Excel down
the further down and further across you go.

What was the formulae that you were using in the 5th column?

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

 

[rcl2884]

Thanks for your interest.

I have had google desktop problems in the past, and so had uninstalled
it.

My function was something like:

Max(Min(A2*B2,$A1) - B$1,0)

Very simple.... I repeated this exercise on two different computers
with the same results. Again, the exact same number of calculations
in four sets of columns and about 30,000 rows was more than 4000 times
faster than two sets of columns and 60,000 rows.

 

[Charles Williams]

I created 4 columns of 60000 rows of random numbers and added
=Max(Min(A2*B2,$A1) - B$1,0) in column E
=MAX(MIN(B2*C2,$A1)-C$1,0) in column F
=MAX(MIN(C2*D2,$A1)-D$1,0) in column G
then copied the formulae down the 60000 rows.

Full Calculation took 0.09 seconds

Why is this not the same as your experience?

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

Thanks for your interest.

I have had google desktop problems in the past, and so had uninstalled
it.

My function was something like:

Max(Min(A2*B2,$A1) - B$1,0)

Very simple.... I repeated this exercise on two different computers
with the same results. Again, the exact same number of calculations
in four sets of columns and about 30,000 rows was more than 4000 times
faster than two sets of columns and 60,000 rows.

 

[rcl2884]

Why is this not the same as your experience?

Boy, I wish I new the answer to that.

I tried the spreadsheet on a third computer, running Excel 2000, and
found the re-calc almost instant. (0.02 seconds).

What version were you running?

 

[Charles Williams]

I was using Excel 2003.

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