Results 1 to 6 of 6

20160405, 20:31 #1
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 972
 Thanks
 53
 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 ?
ThanksLast edited by MartinM; 20160405 at 20:37.

20160405, 20:52 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 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"

The Following User Says Thank You to kweaver For This Useful Post:
MartinM (20160406)

20160406, 04:48 #3
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 972
 Thanks
 53
 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 longforgotten column of over 1,000 historical SUMPRODUCT formulas which I have simply turned into values. That helped too !
On with the task . . .

20160406, 08:39 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 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 userdefined 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

The Following User Says Thank You to zeddy For This Useful Post:
kweaver (20160406)

20160406, 20:08 #5
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 972
 Thanks
 53
 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.

20160407, 04:15 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Hi Martin
10 times faster is not to be sniffed at.
Performance enhancement is where I get all the fun in Excel.
Re: yourbutton to press if this data really does need recalculation
zeddy