+ Reply to Thread
Results 1 to 4 of 4

shorting big summary files refresh time?

  1. #1
    Registered User
    Join Date
    01-01-2009
    Location
    Haifa, Israel
    MS-Off Ver
    Excel 2007
    Posts
    27

    shorting big summary files refresh time?

    Hi again
    I'm working on a monster file having a RAW DATA sheet, analysis sheets and a summary sheet which "milks" the relevant outputs into the preset table’s presentation. The point is the Milking is "alive" and not "copy/paste" like such that any new analysis is immediately summarized. It's only an 8M file but it freezes for "computing cells" every 20-30 second for about 30 seconds. This has a good potential for driving a person crazy. Any idea why is it so slow? Any way to set the update-time to longer time intervals or to update only cells which origin has been changed lately?

    thnks all

    yoniman
    Last edited by yoniman; 03-24-2009 at 07:04 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: shorting big summary files refresh time?

    Possible Causes:
    Volatility
    Most likely you have laced the doc with some volatile functions, functions that recalculate every time the sheet recalcs, whether they need to or not.

    Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()

    Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()

    Array functions used for "milking"
    CSE formulas or a massive use of SUMPRODUCT type formulas can drastically effect large datasheet analysis.

    Solutions: - simplest is to turn off Automatic calculation. Tools > Options > Calculation > Manual ...but that takes some getting used to since the data no longer updates in real-time. You will have to remember to press F9 anytime you want the results refreshed.

    Other - redesign your formulas to use fewer volatile functions and less/no array formulas. This often requires addition of helper columns to the original data so array formulas no longer need to figure out everything for themselves. Instead of having each cell "milk" certain values over and over again for thousands of cells, you can do some evaluations once in the raw data itself as another column, then simply sum/refer to the already evaluated data in the summary.

    Post up a sample of your doc and I'm sure we can offer some streamlining suggestions if this doesn't give you enough to suss it out on your own.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-01-2009
    Location
    Haifa, Israel
    MS-Off Ver
    Excel 2007
    Posts
    27

    Smile Re: shorting big summary files refresh time?

    Ohhhh JBeaucaire - this is exactly what I wanted!
    I'll just check to make sure that turning off the auto-calc affects the specific file and not all of them.

    u can't believe how happy u made me - and u r right: there are many OFFSET, INDIRECT and LOOKUPs in this file.

    thank u!!!

    p.s.
    thankx for the offer but this file is way toooo heavy to rebuilt again. I will however keep your advices in mind for next time

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: shorting big summary files refresh time?

    Manual calculation is a global setting. Bummer on that one. I suppose you could create a couple of macros in this sheet that sets the Calc to Manual when you open it, and back to Auto when you close it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1