Seeking Help - My spread sheet is SLOW!!!

[TCarrigan]

I am attempting to do more with Excel 2007 than it was probably meant to do. I have LARGE SUMPRODUCT command refferences and now my spreadsheet is very very slow.

My sheet includes many tabs that all refference back and forth to each other as well as a single entry with approx 500 dependents. The single entries are in approx 400 locations all with the same dependents. If I change the value in Precedents cell, the calculation take several seconds.

This example formula is repeated about 500+ times throughout the sheet.
=IF($BJ4="","",SUMPRODUCT(($D$66:$D$1703=BL$2)*($R$66:$R$1703=$BJ4)*($AA$66:$AA$1703)))

as well as this formula repeated hundreds of times:
=SUMPRODUCT(('Misc Cable Configurator'!$N$66:$N$1703='Itemized Report'!$D$33)*('Misc Cable Configurator'!$C$66:$C$1703='Itemized Report'!$D54)*('Misc Cable Configurator'!$E$66:$E$1703=1)*('Misc Cable Configurator'!G$66:G$1703)*('Misc Cable Configurator'!$E$66:$E$1703))+SUMPRODUCT(('Misc Cable Configurator'!$N$66:$N$1703='Itemized Report'!$D$33)*('Misc Cable Configurator'!$C$66:$C$1703='Itemized Report'!$D54)*('Misc Cable Configurator'!G$66:G$1703)*('Misc Cable Configurator'!$E$66:$E$1703>1)*(1))+SUMPRODUCT(('Misc Cable Configurator'!$N$66:$N$1703='Itemized Report'!$D$33)*('Misc Cable Configurator'!$C$66:$C$1703='Itemized Report'!$D54)*('Misc Cable Configurator'!G$66:G$1703)*('Misc Cable Configurator'!$E$66:$E$1703>1)*('Misc Cable Configurator'!$E$66:$E$1703-1)*'Misc Cable Configurator'!$CD$3)

I'm totally lost on how to speed this thing up.

Thanks in advance.

 

[crimson_b1ade]

Consider changing your workbook from Automatic calculation to Manual. This can be done by clicking the Office Button, selecting Excel Options, on the Formulas tab set Workbook calculations to 'Manual'. This way you can control when the workbook is calculated which should allow you to use the workbook more efficiently since you don't have to wait 'til Excel finishes calculating before you can continue with your work.

 

[Yard]

Do you have well-laid out tables in your worksheets? You might find a Pivot Table solution more appropriate, since you would be able to group elements together, filter items in/out and get subtotals of matching criteria.

*Additional thought - do you need to refer all the way to row 1703?

 

[TCarrigan]

I have considered Manual Calculation; however, it is more important to ensure that the Calculation is not ever forgotten.

As far as I know the form is well laid out. Being that it's running slow, I'm not sure how well I've done with the layout. I have also explored Pivot Tables. They worked well; but, it's the same as setting up the form for Manual Calculation. Right? What I'm saying is the first time I enter the data it gives me the desired response. If I change something after that, I need to tell it to calculate again.

Unfortunately, my data DOES run all the way down to row 1703.

I'd love to send this file to someone that is willing to take a look at it.

 

[FastExcel]

The trick is to look at the number of calculations you are asking SUMPRODUCT to do (roughly number of rows * number of conditions) and think of ways of reducing them

Here are 2 approaches that should help:

1. Get rid of repeated calculations by moving them out of your SUMPRODUCTs to helper columns or cells.
For instance it looks like this:
'Misc Cable Configurator'!$N$66:$N$1703='Itemized Report'!$D$33
occurs lots of times, and each time its doing over 1600 calculations.
Add a helper column containing the formula
=if('Itemized Report'!$D$33=$N66,1,0) and copy down, then reference it in your SUMPRODUCTs

2. Look at sorting your data and then making your SUMPRODUCTs only reference the appropriate subset of the sorted data.

 

[mgirvin]

Wow, FastExcel,

The helper column is a great idea! I had not thought of that before except for avoiding the old 7 IF Nested function limit.

Thank you for that valuable idea.

 

[350Z Pilot]

I too have a large, multi tab workbook with several calculations. Today my records exceeded 12,000 rows! I also use a lot of helper columns but with this many records even those are overwhelmed. I am more interested in allocating resources to Excel. Anyone know how to do that?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[FastExcel]

Just because you have 12000 rows there is no need for your spreadsheet to calculate slowly.
Although buying a faster PC will help a little with calculation speed, by far the biggest gains can be made by:
- finding and prioritising your calculation bottlenecks
- working out how to minimise or eliminate them

Take a look at my white paper on Optimising Excel Calculation speed
http://msdn.microsoft.com/en-us/library/aa730921.aspx
(although it talks about Excel 2007 most of the article is relevant to all Excel versions)
and at my Excel pages
http://www.decisionmodels.com/

 

[350Z Pilot]

I appreciate your input and will read the white paper. I should have stated that I have 12,000 records today each having 56 columns of data. I then run 46 separate calculations for each record (and this is just on one tab). My computer is ok with Core2 CPU @ 2.13GHz and 4 GB of RAM. I employ several tricks to reduce calc time. For example; I only have one row of calculations when opening a new template. After all my files are imported I run a macro that sets calc to manual, copies the formulas, then performs a calculation. My template works well up to about 7,000 records but after that I experience slowness. This is partially due to the fact that data on my other tabs grows exponentially. In the old days you could allocate system resources to specific programs (which is the area of advice I was looking for) but I guess that is no longer possible.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

 

[DonkeyOte]

When using lots & lots of SUMPRODUCTS you need to revise your approach and use a different method.. Pivot Tables is obvious choice however not always applicable... less used but equally effective is to use a helper column but use it in such a way as to Concatenate those fields you're using in your Sumproduct... you can then revert to native SUMIF / COUNTIF approches utilising the Concatenation key.

Consider:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Fld1	Fld2	Fld3	Fld4	Fld5	KEY		Criteria 1	Criteria 2	Criteria 3	Criteria 4	Sumproduct	W/Key
2	D	C	C	C	69	D:C:C:C		A	A	B	B	226	226
3	D	D	C	D	34	D:D:C:D		A	A		B		320	Criteria 3 irrelevant...
4	D	C	C	A	93	D:C:C:A
5	A	C	C	C	63	A:C:C:C
6	C	A	B	C	71	C:A:B:C
7	A	A	B	B	89	A:A:B:B
8	B	A	C	C	39	B:A:C:C
9	B	C	B	B	45	B:C:B:B
10	D	A	B	A	66	D:A:B:A
11	C	C	B	A	15	C:C:B:A
12	B	C	C	D	99	B:C:C:D
13	C	C	B	D	45	C:C:B:D
14	C	C	C	A	13	C:C:C:A
15	A	A	B	B	74	A:A:B:B
16	A	A	X	B	94	A:A:X:B
17	A	A	B	B	63	A:A:B:B
18	B	C	B	B	16	B:C:B:B
19	B	C	B	A	39	B:C:B:A
20	C	B	B	B	96	C:B:B:B
Sheet1

You can see the flexibility offered using a concatenation key with use of wildcards (see M3) ... I regularly help people out who have large spreadsheets resulting from lots of large arrays and this is the way to go... alas elegant and efficient don't always go hand in hand in XL terms...