+ Reply to Thread
Results 1 to 11 of 11

PC Configuration

  1. #1
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    PC Configuration

    Apologies if this is in the wrong forum...

    I currently use a Core2 Duo PC and often max out the both CPU for extended periods when recalculating large (30MB+) spreadsheets.

    Wondering if anyone has experience with Quad Core desktop PC's and large files and if there is a noticeable / significant performance improvement with more cores..

    Ram doesn't seem to be an issue.

    Any feedback welcome.
    Last edited by Jbentley; 12-20-2009 at 08:28 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,686

    Re: PC Configuration

    A 30 mb workbook seems very large, is there a reason it is so large, usually such a large workbook means there are other issues.

  3. #3
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: PC Configuration

    hi,

    I agree with Dave, there are probably other issues & here are some links about potential issues which make interesting reading...

    For general speed issues have a look at www.mvps.org/dmcritchie/excel/slowresp.htm

    I suspect that it's the actual formulae within your file that is slowing things down. Do you use any/many Sumproduct, [ctrl + shift + enter] Array formulae, or Indirect formulae etc...?
    I suggest reading the below & all the pages that are listed in the menu across the top of each of the below pages on Charles' site:
    http://www.decisionmodels.com/calcsecrets.htm
    http://www.decisionmodels.com/optspeed.htm

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  4. #4
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: PC Configuration

    Hi,

    Thanks Dave & Rob - will take have a good read of Charles' site.
    One of the main reasons the files are so large is due to dealing with a couple hundred thousand rows of customer details & marrying up behavoural metrics to form customer classes / clusters.
    I do use a lot of Sumproduct, & more recently (since moving to '07) a lot of SumIFs, AvgIfs etc. Guilty of DSUM, DAverage too - but don't use these that often.

    I avoid inter file formulaic references, but do use a lot of intra file references.

    Sometimes the file size is unaviodable - and am thinking it may ultimately be a question of sheer CPU grunt.

    Thanks for the advice,
    Justin

  5. #5
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Akron, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: PC Configuration

    Have you considered using a database to store the customer info and then pulling parts of it into excel when needed?

  6. #6
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: PC Configuration

    Hi jrdnoland,
    Yeah, I do use db, forgot to mention that up front.
    The dataset is a small subset of customers in a datawarehouse (+1M unique customers)
    Generally mining the warehouse is Ok, but sometimes I need to work around the referential intergrity of the various tables to build new, temporary connections between customers, customer data and other less tangibly connected datasources. Excel has the greatest flexibility for this.

    Cheers,
    JB

  7. #7
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: PC Configuration

    hi Justin,

    Quote Originally Posted by Jbentley View Post
    Hi,
    I do use a lot of Sumproduct, & more recently (since moving to '07) a lot of SumIFs, AvgIfs etc. Guilty of DSUM, DAverage too - but don't use these that often.
    I haven't used them at all myself, but I think I've read somewhere that Dsum & Daverage may be "better" than other approaches in some situations.
    Which ever formulae you do use, always (?) try to limit the range that the functions have to refer to (ie only the used range rather than full column references).

    hth
    Rob

  8. #8
    Registered User
    Join Date
    12-01-2009
    Location
    Oklahoma City
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: PC Configuration

    Apparently Excel 2007 is the first version to support multithreading. I can't find any benchmarks to compare, though, in general, quad core processors are very noticeably faster than duo cores in multithread applications. I'm not saying twice as fast, but maybe 60-70% faster?

    Found one,

    http://www.xbitlabs.com/images/cpu/c...6600/excel.png

    from Multi-Core Confrontation: Core 2 Quad Q6600 vs. Core 2 Duo E6850
    Last edited by jevery; 12-20-2009 at 12:58 PM.

  9. #9
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: PC Configuration

    hi all,

    Jevery, here are some links discussing multi-threading in Excel...

    http://www.pcreview.co.uk/forums/thread-3731633.php
    http://www.mrexcel.com/forum/showthread.php?t=297987
    Probably the most useful below & note that some Excel functionality doesn't support multi-threading...
    http://www.decisionmodels.com/calcsecretsc.htm
    (I recommend reading the links on this site too)

    hth
    Rob
    Last edited by broro183; 12-20-2009 at 07:17 PM.

  10. #10
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: PC Configuration

    Hi,
    Thanks Rob & Jevery,

    Rob: I used to use Dimensional Aggregates a lot (before I wrapped my head around Sumproduct) but found that, for some reason I couldn't fathom, that string criteria wasn't being assessed correctly. eg. I had "Act" & "Actx" as criteria then results for "Actx" were also included in results for "Act".

    Jevery: Cheers! - hoping to build a case for IT dept to spring for a Quad core for me - this will add to conversation.

  11. #11
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: PC Configuration

    Thanks to all that have added comment and advice to this thread.
    I think I've enough to go on now - so am marking as SOLVED

+ 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