Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Performance issue with Named Ranges

    I have a fairly big Worksheet with around 6,000 rows.

    16 of the columns are separate Named Ranges, all the same height.

    I read some time ago (RG I think) that performance would be improved if I just used one Named Range, encompassing all the columns needed.

    My question is this: if I do that, how do I reference just one of the columns of the new Named Range in a Worksheet formula, for instance SUMPRODUCT ?

    Thanks
    Last edited by MartinM; 2016-04-05 at 21:37.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    If I understand your question, consider using INDEX(MyRange,,3) to use column 3 of the named range "MyRange"

  3. The Following User Says Thank You to kweaver For This Useful Post:

    MartinM (2016-04-06)

  4. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    That's it - many thanks.

    I'm gradually getting to grips with the slowness in recalculation - it had got to a ridiculous 8 seconds at one point, now down to 0.4 seconds and more work to do.

    I've written a macro to enumerate the calculation time for each Worksheet and that's helping me with the diagnosis - including finding a long-forgotten column of over 1,000 historical SUMPRODUCT formulas which I have simply turned into values. That helped too !

    On with the task . . .

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Martin

    I think it is always better to avoid complex formulas, and especially array formulas if possible i.e. it is better to use more helper columns to simplify formulas (easier to follow and debug). Excel is now much smarter at optimising recalculation, especially with it's multithreaded operation.

    It is sometimes possible to replace a slow array formula with a faster user-defined function.
    For example, to count the number of unique entries in an unsorted list of 6,000 rows takes ~8.0 seconds using an array formula, takes ~6.0 seconds using a SUMPRODUCT formula, but only ~0.04 seconds using a custom function. (If the 6,000 rows were already sorted, then using a simple formula in an extra column, and then summing those formulas, would take ~0.016 sec).

    Converting complex formulas to values always gives a great performance boost.
    For example, instead of using formulas to 'fetch' values from an external workbook, I always use vba to 'grab the block of data, import it and paste as values, then close the data source workbook etc etc etc)

    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    kweaver (2016-04-06)

  7. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thankz for thoze inzightz.

    I am steadily working my way through sort of things you have mentioned . . .

    • Somewhat to my surprise (and disappointment - it was a lot of work!) using a single range and referencing specific columns within it (instead of using a bunch of propinquitous ranges) made no significant difference. As the separate ranges had meaningful titles they made reading and checking formulas much easier, so I've dropped that idea.
    • Using INDEX and MATCH constructs turns out to be much faster than INDIRECT and OFFSET methods and I am converting those as I work out how to do each one. I've just a few I'm struggling with but I'll get there eventually.
    • Most of the data is historic so I have, as you suggest, simply turned those calculations into values - they will never change.
    • Also, much of the calculation is "static" insofar as the results don't have to change on the hoof during a session. I've tackled this by doing those calculations in VBA when the Workbook is opened - and have provided a button to press if this data really does need recalculation within a session.
    • I have already a couple of UDFs for my most common tasks - CellAbove (see below) and SUBTOTALIF.

    At the time of writing the recalculation time is exactly 10 times faster than it was three days ago !

    That leaves . . .

    • A few very large SUMPRODUCT arrays to sort out and I will work on some UDFs for those
    • A oddity - I am using extensively a CellAbove name that RG suggested some years ago - it avoids the issue of a SUM or SUBTOTAL range not extending properly when rows are added. The definition is =INDIRECT("R[-1]C",0) and I don't think there's a faster way of doing this ?

    As I said earlier, the Macro which records calculation times sheet by sheet has also been invaluable in pinpointing my worst bad practices so that I can prioritise my efforts.

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Martin

    10 times faster is not to be sniffed at.
    Performance enhancement is where I get all the fun in Excel.
    Re: your
    button to press if this data really does need recalculation
    ..I tend to label this button as [Refresh]

    zeddy

Posting Permissions

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