SumProduct Function - Drags Excel to its knees

D

Don Rist

Greetings All,

I need help with a really slow spreadsheet. It consist of a database
of 6 columns and 1400 rows that feeds about 6 other sheets that contain
5500 instances of the SumProduct function. Each instance of the
SumProduct function checks values in three columns of the datasheet.
Recalculation takes about 10 to 15 seconds on a pretty fast machine with
lots of memory. I've tried setting the recalculation options to ignore
data tables but that's not an option when using the spreadsheet for its
intended purpose.

Any ideas or help would be greatly appreciated.
 
T

Tushar Mehta

Greetings All,

I need help with a really slow spreadsheet. It consist of a database
of 6 columns and 1400 rows that feeds about 6 other sheets that contain
5500 instances of the SumProduct function. Each instance of the
SumProduct function checks values in three columns of the datasheet.
Recalculation takes about 10 to 15 seconds on a pretty fast machine with
lots of memory. I've tried setting the recalculation options to ignore
data tables but that's not an option when using the spreadsheet for its
intended purpose.

Any ideas or help would be greatly appreciated.
Click to expand...
It is possible, though hard to tell with any certainty w/o more information
about what the formulae do, that treating the table as a SQL/ODBC data
source might speed things up a lot.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
D

Don Rist

Biff,
Thanks for the link to decision models. It gave me some good
background information on what was causing my recalculation to take so
long. I ended up changing each SumProduct equation to look at 400 rows
instead of 1400 rows. The recalculation time went from 10 seconds to
barely perceptible.
Regards,
Don
 
D

Don Rist

Hi Tushar,
Thanks for the reply and the idea. Changing my data source was not an
option in this case. All the data was in Excel and I don't know enough
to change it to an SQL/OBDC data source. I ended up using a simple
solution of segmenting the database. Instead of the 1400 data rows I
reduced it to about 350 rows per segment. The recalculation speed
increase was just amazing.
Regards,
Don
 
T

Tushar Mehta

Thanks for sharing how you solved the problem.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hi Tushar,
Thanks for the reply and the idea. Changing my data source was not an
option in this case. All the data was in Excel and I don't know enough
to change it to an SQL/OBDC data source. I ended up using a simple
solution of segmenting the database. Instead of the 1400 data rows I
reduced it to about 350 rows per segment. The recalculation speed
increase was just amazing.
Regards,
Don
Click to expand...
{snip}
 
Top