+ Reply to Thread
Results 1 to 14 of 14

Calculation Time

  1. #1
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Calculation Time

    So I have a strange problem... I'm using excel 2007 and I have a sumif formula that calculates through about 5000 rows of data.

    One row has this formula in it:
    =SUMIF($G$1:$G1,$G2,L$1:L1)+SUMIF($H$1:$H1,$G2,AB$1:AB1)

    Another row has almost the exact same formula but reversed:
    =SUMIF($H$1:$H1,$G2,L$1:L1)+SUMIF($G$1:$G1,$G2,AB$1:AB1)

    The formula is copied all the way down 5000+ rows in each column. Here's my problem -- in the first column the calculations take a long time and after they finish and I attempt to do a paste special->values excel takes FOREVER to accomplish this (like 3+ hours). In the other column with almost the exact same formula calculation time is quick and paste special->values is instantaneous. What's up?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Calculation Time

    Hello roasthawg,

    How Excel determines what to calculate and when is done by a complex set of algorithms. Many factors influence this decision process. It is certainly interesting that the formula time results are so different, but as to exactly why such a discrepancy exists may be unknowable.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculation Time

    But what about the paste special->values? Why would one be almost instantaneous and the other take hours?? This is the same exact formula on the exact same amount of cells just flip-flopped.

  4. #4
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculation Time

    I have something to add to this as I'm messing with it right now... I can use paste special->values to copy the information to another worksheet and it is lightening quick. But when I go back and try to clear out the cells containing the formulas excel just freezes up.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,447

    Re: Calculation Time

    Turn your calculation to manual and see if that changes things.
    Using VBA you can calculate a range. If you can write a little code you can test ranges of your data by only calculating them. Like from the immediate windwo type -
    Please Login or Register  to view this content.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculation Time

    As has been outlined by Leith a huge number of factors can impact calculation performance.

    Pasting values will invoke a Calculation - what is interesting is that the issue only relates to one column which implies the issue is not one of Volatility (as this would be reflected in changes to either column)

    You could perhaps test to see how many dependants you have of the "slow" column - these will recalculate when you modify the precedent cells (running on Auto Calc [as you should IMO])
    If the dependant formulae are "expensive" calcs. in their own right that could be part of the problem.

    Other things perhaps worth checking - do you have conditional formatting applied to the "slow" column (and not the other) ?

    On a final note - you don't stipulate in which columns the formulae reside - I am assuming the references are not circular in the "slow" column ?

    edit: another thing that could be an issue based on the OP would be an inefficient Change event if one exists and is specific to the "slow" column (copy/pasting the formula and/or results would invoke this)
    Last edited by DonkeyOte; 10-14-2010 at 03:00 AM.

  7. #7
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculation Time

    No luck so far... followed the suggestions in the two posts above but the problem persists. I should point out that the data that I'm performing the calculations on has been "scraped" from the internet to a mysql database before being exported to excel in ms excel 2000 format. Not sure if this can bring any formatting issues into play but I haven't been able to find them yet if they do exist.

  8. #8
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculation Time

    One more thing.... the spreadsheet is only 34,000 kb yet it takes FOREVER to load. The loading bar goes up rather quickly until just before it's open and then it just freezes there for like 20 minutes.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculation Time

    Quote Originally Posted by roasthawg
    ...the data that I'm performing the calculations on has been "scraped" from the internet to a mysql database before being exported to excel in ms excel 2000 format
    when you say exported what do you mean exactly from a workflow perspective ?
    Are you running CSV exports from MySQL Query Browser or equiv. or do you have connections setup in Excel that link to your MySQL tables ?
    (If you do have connections are they set to Refresh on Open ?)

    To be honest without seeing something it's going to be tricky to nail this one down...

    If you create a new workbook with just the two columns of formulae and the precedent range values what is the calculation time like then ?

  10. #10
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculation Time

    Quote Originally Posted by DonkeyOte View Post
    when you say exported what do you mean exactly from a workflow perspective ?
    Are you running CSV exports from MySQL Query Browser or equiv. or do you have connections setup in Excel that link to your MySQL tables ?
    (If you do have connections are they set to Refresh on Open ?)

    To be honest without seeing something it's going to be tricky to nail this one down...

    If you create a new workbook with just the two columns of formulae and the precedent range values what is the calculation time like then ?
    I'm using phpMyAdmin to export the data... they have an "export as MS Excel 2000" feature.

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculation Time

    OK. Thanks.

    I'd still try replicating the data in a new file to see if the issue is specific to the calculations themselves or to the workbook.

    The open issue sounds odd... but again with no access it's hard to really offer any advice.
    If you have lots of (complex) Volatiles then this could slow things down on open, however, as mentioned before were Volatility an issue I don't think you'd be experiencing the "one column" slowdown you mention - it would affect both columns.

  12. #12
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculation Time

    Is it possible that a sumif with 5,000 rows is just at the edge of what Excel can handle?

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculation Time

    I doubt SUMIF in it's own right is the underlying problem though admittedly 10000 separate SUMIF functions each referencing increasingly large ranges is not ideal.
    The SUMIF function itself is hugely efficient and calculates based on the intersect of precedent ranges and Used Range.

    It's quite possible you could improve the formula but again it's a case of being able to see the setup in order to comment (decisively) on possible alternatives.

  14. #14
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculation Time

    Is there anyway you can upload my entire 34000 kb file and check it out?

+ 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