Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Our new forecast workbook is quite a doozie. Its got 37 departmental income statements (detail by account) and 12 division/summary sheets. Each sheet (23x453) is filled (mostly) with lookups and sumifs, sums, multiples, divisibles, formatting and you name it. File-Properties lists it at 23.3 mb, a real monster (at least by my standards).

    So far (I just created it last week) it does almost everything I need, at least for forecasting, but I may need to expand the workbook with supplemental reports.

    Recently the company upgraded our back-office accounting package with a product (COGNOS) that acts as an add-in with Excel to extract data from our i-Series Infinium. But with the mega-workbook Iíve got going, I canít take advantage of the features. I can only assume that my pc is restricted by available processing power. I had to bypass some of the features of the new add-in by adding a step and a new workbook to the update process because the spreadsheet appeared to stall at the part where data is moving from our i-Series to our network (rated at 100Mbps over fiber). At some points Task Managerís CPU Usage graphical display shows one processor pegged at or near the top of the chart while the other is running about mid-way.

    My desktop pc uses an Intel Core2 running at 2.13 Ghz and just had an additional 2 Gb ram installed (reporting a total of 2.99).

    I think I have convinced my boss that I need a pc with more processing power, but Iím not sure whether I should ask for a Core2 with a higher clock-speed or one of the Intel Quad Core Zeon processors. I will probably be limited in price.
    Some of the low-end Quads appear to be about the same cost as some of the high-end Core2ís.

    Which is better? Do Quads help in spreadsheet processing or should I just pick anything with a higher clock speed?
    Do you know of any documentation available which might help me.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sounds like your file is rather calculation intensive. There is a lot to improve by optimising you formulas, as much as mabe a factor 10 in some occasions.
    Do you also use user defined functions ?
    One point to start your optimisation is by changing all your VLOOKUPS. I bet you are using them all with the last argument set to false, thus rendering an exact match, right? That is a very slow way of looking up information.

    You can improve lookup performance a lot by:

    - Sorting your data on the lookup column
    - using a formula like this (looks up value in column A, returns value from column B ):

    =IF(INDEX($A2$:$A100,MATCH(C1,$A2$:$A100,1))=C1,IN DEX($B2$:$B100,MATCH(C1,$A2$:$A100,1)),NA())
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,592
    Thanks
    5
    Thanked 1,059 Times in 928 Posts
    [quote name='Arcturus16a' post='779947' date='15-Jun-2009 13:29']I think I have convinced my boss that I need a pc with more processing power, but I'm not sure whether I should ask for a Core2 with a higher clock-speed or one of the Intel Quad Core Zeon processors. I will probably be limited in price.
    Some of the low-end Quads appear to be about the same cost as some of the high-end Core2's.

    Which is better? Do Quads help in spreadsheet processing or should I just pick anything with a higher clock speed?
    Do you know of any documentation available which might help me.[/quote]

    It is unlikely that a new PC will do much real good. There are several things to consider when trying to diagnose performance issues in addition to streamlining the workbook.

    1.) Remember your PC on the network is really only as fast as the slowest connection. Unless all connections are 100 Mbs there is no way your PC is going to receive data approaching that speed. Also, network speeds are usually expressed in theoretical maximums and are seldom achieved. When they are achieved it is usually in short bursts. You'd need to use network monitoring software to accurately analyze the network speed.
    2.) There is almost no application software out right now that takes advantage of multi-core processors. The only advantage you'll see is if Windows can take advantage on the cores.
    3.) Other applications that are running can greatly affect your perceived performance. Do you typically have other applications running when you are running Excel?
    4.) Do you have the most up-to-date network drivers for your NIC?
    5.) If you are changing many values in the spreadsheet you may want to turn off automatic recalculation while the iSeries data is transferred. Then manually recalculate and turn it back on if needed.
    6.) Which version of Excel are you using? Is it up-to-date with service packs and patches?

    Joe
    Joe

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks to both Jan and Joe for your help.

    I'm looking forward to applying the INDEX formula to see how that helps. The LOOKUPS I used (37 of them) were of the simple variety and have now all been converted to values.

    As for the network and NIC drivers, I'll have to pass that info on to our IT person, but I think we're pretty up to date on those matters.

    I close all applications as well as other workbooks that I don't need while using the forecast file because of the size of the spreadsheet. I have eliminated as many formulas as possible (I suppose I could look it over again) and reset the used range to the last used cell on every sheet.

    The Excel version is 2003 (Office 2003 Service Pack 3 (SP3))

    Probably the biggest speed improvement to the overall process will be to remember to go to Manual Calc before I try updating from the i-Series (then go to lunch).

    I'll update this post if I stumble across any significant improvements based on your suggestions or otherwise.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Excel 2003 is single threaded, so I would go for a faster processor over multiple cores as a rule.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Also, you may want to look at FastExcel and all the information at Decision Models.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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