Excel performance running slow

[juliejg1]

I am working on a workbook thta contains 4 worksheets. Each worksheet has
anywhere from 10-30 columns. Each column contains a formula, some are quite
lenghty. Most are calculations of some sort. Most formulas reference 2
worksheets. This is going to be used by others as a template so all formulas
have been copied down 10000 rows. The workbook is running quite slow.
Especially when entering data, it take awhile to calculate. I would like to
know what the main cause of the time lag is in Excel so that I can modify the
workbook to run faster. I am assuming it is the formulas but am not sure if
it could be the number of rows, number of worksheets, etc...
Also, my computer is in good performance and has enough space and ram and
has been defragged.

 

[Dave F]

You may have volatile functions in your formulas.

Post examples of your formulas.

Dave

 

[juliejg1]

Here's some of the formulas:
=IF(Data!O6<13,(Data!P6-Q6)*Data!R6,(Data!P6-Q6)*Data!R6/Data!O6*12)

=SUMIF(Details!AG$5:BS$5,">="&CurDate,Details!AG6:BS6)

=IF(Scorecard!$B$4>=Data!I6,IF(Data!N6>=Data!M6,MIN(Data!N6-Data!M6,DAYS360(Data!I6,Scorecard!$B$4,FALSE))/30*(AB6/Data!O6)-MIN(Data!N6-Data!M6,MAX(DAYS360(Data!I6+Data!O6*30,Scorecard!$B$4,FALSE),0))/30*(AB6/Data!O6),MIN(Data!M6-Data!N6,DAYS360(Data!I6,Scorecard!$B$4,FALSE))/30*(-AB6/Data!O6)+MIN(Data!M6-Data!N6,MAX(DAYS360(Data!I6+Data!O6*30,Scorecard!$B$4,FALSE),0))/30*(AB6/Data!O6)),0)

=IF(AI$5>=Data!$I6,IF(DAYS360(Data!$I6,AI$5,FALSE)<(30*Data!$O6),IF(AI$5<Data!$I6+365,$Y6/Data!$O6,0),0),0)


=SUMIF(Details!$N$6:$N$20000,">"&60,Details!$AH$6:$AH$20000)-SUMIF(Details!$N$6:$N$20000,">"&76,Details!$AH$6:$AH$20000)

=SUMIF(Data!$N$6:$N$20000,">"&75,Details!$AG$6:$AG$20000)-SUMIF(Data!$N$6:$N$20000,">"&91,Details!$AG$6:$AG$20000)

=SUMPRODUCT((Details!$V$6:$V$20000="Yes")*(Details!$AH$6:$AH$20000>0))

 

[Dave F]

What version of Excel are you using? Your workbook is creating tens
of thousands of dependencies. Older versions of Excel (before XL
2007) have a limit of 65,000 dependencies or so.

 

[juliejg1]

Im using 2003

What version of Excel are you using? Your workbook is creating tens
of thousands of dependencies. Older versions of Excel (before XL
2007) have a limit of 65,000 dependencies or so.

 

[Charles Williams]

You could try looking at
http://msdn2.microsoft.com/en-us/library/aa730921.aspx
which contains some advice applicable to all Excel vcersions for identifying
where the slow calculations are (bottlenecks)
the article also contains some advice on speeding up the bottlenecks
or
http://www.DecisionModels.com

has more advice


Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com