Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Workbook file size is insane, calcs take forever
Hi all -
I have built a rather complicated spreadsheet that basically calculates and archives all the stats for a disc golf league. It accommodates 6 divisions of up to 100 players each. It took me a couple of months to build, and before I knew it, I was up to 50 worksheets, 600 named ranges (dynamic and otherwise), and 1000s of rows and columns filled with different repeated formulas, almost all with an error trap (IF(ISERROR mostly). It automatically does all the finances, player ratings, points tracking, scoring, prize payouts including splitting ties, player rankings, dozens of stats, and much more. All based on just the entering of a players' name and score. The results of much of it can be seen at www.sundogtournamentseries.com/standings.htm My problem is that the file size has reached a ridiculous 19MB and it pushes hard on my CPU for an increasingly long time for every calculation upon data entry. It's gotten to the point where I have to manually calc after entering scores and stats. It can take 20 seconds on my laptop. Being self taught, I'm still kind of a novice at some aspects of the software. I've read some online but haven't found much help from the basics of workbook configuration and keeping file sizes down. I have decent habits so I've already done most of the suggestions I've found. I'm wondering if there are any more advanced tips for making something this complex more manageable. I thought of splitting sections into different workbooks, but with all workbooks active during use I thought I would still run into the same problems. Also, eventually I want to evolve to an online clickable player database, will Excel still be my best bet? Any help is appreciated. Thank you in advance for any help, MAK |
#2
|
|||
|
|||
Hi!
Considering the file size and the amount of formulas, etc., 20 sec calc times aren't "unreasonable". Try to limit the number of dynamic ranges and the use of volatile functions. Lots of good techniques he http://www.decisionmodels.com/ Biff "Matt K" wrote in message . .. Hi all - I have built a rather complicated spreadsheet that basically calculates and archives all the stats for a disc golf league. It accommodates 6 divisions of up to 100 players each. It took me a couple of months to build, and before I knew it, I was up to 50 worksheets, 600 named ranges (dynamic and otherwise), and 1000s of rows and columns filled with different repeated formulas, almost all with an error trap (IF(ISERROR mostly). It automatically does all the finances, player ratings, points tracking, scoring, prize payouts including splitting ties, player rankings, dozens of stats, and much more. All based on just the entering of a players' name and score. The results of much of it can be seen at www.sundogtournamentseries.com/standings.htm My problem is that the file size has reached a ridiculous 19MB and it pushes hard on my CPU for an increasingly long time for every calculation upon data entry. It's gotten to the point where I have to manually calc after entering scores and stats. It can take 20 seconds on my laptop. Being self taught, I'm still kind of a novice at some aspects of the software. I've read some online but haven't found much help from the basics of workbook configuration and keeping file sizes down. I have decent habits so I've already done most of the suggestions I've found. I'm wondering if there are any more advanced tips for making something this complex more manageable. I thought of splitting sections into different workbooks, but with all workbooks active during use I thought I would still run into the same problems. Also, eventually I want to evolve to an online clickable player database, will Excel still be my best bet? Any help is appreciated. Thank you in advance for any help, MAK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I deleted 99% of data from excel file. Folder Size remains unchang | Excel Discussion (Misc queries) | |||
Why has file size jumped in an Excel workbook? | Excel Discussion (Misc queries) | |||
Massive large size of Excel File after Updating | Excel Discussion (Misc queries) | |||
Excel file size | Excel Discussion (Misc queries) | |||
WHY IS MY FILE SO BIG? How can I reduce the size of a file? I h. | Excel Discussion (Misc queries) |