Excel performance running slow

  • Thread starter Thread starter juliejg1
  • Start date Start date
J

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.
 
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))
 
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.
 
Im using 2003

Dave F said:
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.
Click to expand...
 
Back
Top