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

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