Dynamic Ranges: Speed Issue

[Sige]

Hi There,

I have a NAME called "TESTRANGE"
='Select Products'!C26:O1500

I would like to make this range dynamic as the nr of items in the range
could vary a bit ...
I tried a couple of ways:
=OFFSET('Select Products'!$C$26,0,0,COUNTA('Select Products'!$B:$B),13)

=OFFSET('Select Products'!$C$26;0;0;Dynamic_Range;13)
with Dynamic_Range containing the nr of rows for the range (for above
eg: 1500)

=INDIRECT("'Select Products'!$C$26:$O$"&LROW)
with LROW-name containing the last row nr.

WHEN the range is not dynamic, my worksheet calculates my workbook in:
0.01 seconds
Actually I do not know, but let me say instantaneously!
WITH what ever of the above dynamic ranges I could chronometer the
calculation time ...
it took 8.0-8.2 seconds

I do not find the ranges particularly long ... and have just a couple
of them in my workbook, but the overhead to handle these volatile
functions seems to slow the calculation speed dramatically! (What
performance will this give in Excel 12?)

Luckily I kicked the array-formulas already out ...
Anybody suggestions on having dynamic ranges but not slowing down
calculation speed with 1000%?

Brgds Sige

 

[Guest]

What happens if you define the range as either 'Select Products'!C:O or
'Select Products'!C26:O65536? I don't know if that would help, but I'd at
least give it a shot. --Bruce

 

[Aladin Akyurek]

If you are on Excel 2003, try to convert the ranges of interest into
lists by means of Data|List|Create List. The move eliminates largely the
need for dynamic named ranges.

Hi There,

I have a NAME called "TESTRANGE"
='Select Products'!C26:O1500

I would like to make this range dynamic as the nr of items in the range
could vary a bit ...
I tried a couple of ways:
=OFFSET('Select Products'!$C$26,0,0,COUNTA('Select Products'!$B:$B),13)

=OFFSET('Select Products'!$C$26;0;0;Dynamic_Range;13)
with Dynamic_Range containing the nr of rows for the range (for above
eg: 1500)

=INDIRECT("'Select Products'!$C$26:$O$"&LROW)
with LROW-name containing the last row nr.

WHEN the range is not dynamic, my worksheet calculates my workbook in:
0.01 seconds
Actually I do not know, but let me say instantaneously!
WITH what ever of the above dynamic ranges I could chronometer the
calculation time ...
it took 8.0-8.2 seconds

I do not find the ranges particularly long ... and have just a couple
of them in my workbook, but the overhead to handle these volatile
functions seems to slow the calculation speed dramatically! (What
performance will this give in Excel 12?)

Luckily I kicked the array-formulas already out ...
Anybody suggestions on having dynamic ranges but not slowing down
calculation speed with 1000%?

Brgds Sige

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Sige]

Hi Bpeltzer,

Defining the ranges like this helps in speed ...but as I use these
ranges in combo boxes ... I do not want 64000 empty lines in it ;o).

Cheers Sige

 

[Sige]

Hi Aladin,

Lists could indeed be an option, but my wbk should run on XL97.

-Could you show me what you mean under [1] ?
-I have a couple of lookup functions, returning an exact match ...how
do I set an optional argument returning #NA?
Does this increase the calc speed in your opinion?

Best Regards Sige

 

[Aladin Akyurek]

Sorry I didn't notice your reply.

I believe you're referring to the proposals in my sig.

Regarding [1], it just proposes that SumProduct itself reads TRUE as 1
and FALSE as 0 when it has to evaluate conditionals like:
....,(A2:A8="X"),... This has as consequence that TRUE's and FALSE's in
the sum range will also be coerced into 1's and 0's, a behavior that
would make the SUM part of SUMPRODUCT to differ from SUM().

Regarding [2], it's a proposal for extending the syntax of lookup
functions. Exmaples:

=VLOOKUP(V,Table,Idx,0,0)

which would return 0 instead of #N/A.

---------------------------------------

If you have something like:

=VLOOKUP(V,$A$2:$F$1000,3,0)

or

=IF(ISNA(VLOOKUP(V,$A$2:$F$1000,3,0)),ReturnValue,VLOOKUP(V,$A$2:$F$1000,3,0))

and the table $A$2:$F$1000 is sorted on its first column in ascending
order or you're willing to sort it on its first column in ascending
order and maintain it sorted, the following would yield more speed:

=IF(LOOKUP(V,$A$2:$A$1000)=V,LOOKUP(V,$A$2:$A$1000,$A$2:$A$1000),ReturnValue)

This is an efficient idiom I picked up from Charles Williams.

Hi Aladin,

Lists could indeed be an option, but my wbk should run on XL97.

-Could you show me what you mean under [1] ?
-I have a couple of lookup functions, returning an exact match ...how
do I set an optional argument returning #NA?
Does this increase the calc speed in your opinion?

Best Regards Sige

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.