| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel performance running slow
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel performance running slow
You may have volatile functions in your formulas.
Post examples of your formulas. Dave On Dec 18, 1:26 pm, juliejg1 wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel performance running slow
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,MI N(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$200000)) "Dave F" wrote: You may have volatile functions in your formulas. Post examples of your formulas. Dave On Dec 18, 1:26 pm, juliejg1 wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel performance running slow
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. On Dec 18, 3:57 pm, juliejg1 wrote: 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,MI N(Data!N6-Data!M6,DAYS360-(Data!I6,Scorecard!$B$4,FALSE))/30*(AB6/Data!O6)-MIN(Data!N6-Data!M6,MAX(DA-YS360(Data!I6+Data!O6*30,Scorecard!$B$4,FALSE),0))/30*(AB6/Data!O6),MIN(Dat-a!M6-Data!N6,DAYS360(Data!I6,Scorecard!$B$4,FALSE))/30*(-AB6/Data!O6)+MIN(D-ata!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<Da-ta!$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$200000)) "Dave F" wrote: You may have volatile functions in your formulas. Post examples of your formulas. Dave On Dec 18, 1:26 pm, juliejg1 wrote: 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.- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel performance running slow
Im using 2003
"Dave F" wrote: 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. On Dec 18, 3:57 pm, juliejg1 wrote: 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,MI N(Data!N6-Data!M6,DAYS360-(Data!I6,Scorecard!$B$4,FALSE))/30*(AB6/Data!O6)-MIN(Data!N6-Data!M6,MAX(DA-YS360(Data!I6+Data!O6*30,Scorecard!$B$4,FALSE),0))/30*(AB6/Data!O6),MIN(Dat-a!M6-Data!N6,DAYS360(Data!I6,Scorecard!$B$4,FALSE))/30*(-AB6/Data!O6)+MIN(D-ata!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<Da-ta!$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$200000)) "Dave F" wrote: You may have volatile functions in your formulas. Post examples of your formulas. Dave On Dec 18, 1:26 pm, juliejg1 wrote: 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.- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel performance running slow
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 "juliejg1" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel file performance slow | Excel Discussion (Misc queries) | |||
| pivot table from recordset - very slow performance | Charts and Charting in Excel | |||
| Very slow performance while exploring. | Excel Discussion (Misc queries) | |||
| Spreadsheet performance is slow | Excel Worksheet Functions | |||
| excel running slow when saving | Excel Discussion (Misc queries) | |||
Linear Mode
