Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 1st 05, 04:20 PM posted to microsoft.public.excel.worksheet.functions
Sige
 
Posts: n/a
Default Dynamic Ranges: Speed Issue

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


  #2   Report Post  
Old December 1st 05, 04:48 PM posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Dynamic Ranges: Speed Issue

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

"Sige" wrote:

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


  #3   Report Post  
Old December 1st 05, 11:08 PM posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default Dynamic Ranges: Speed Issue

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.

Sige wrote:
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.
  #4   Report Post  
Old December 2nd 05, 01:46 PM posted to microsoft.public.excel.worksheet.functions
Sige
 
Posts: n/a
Default Dynamic Ranges: Speed Issue

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

bpeltzer wrote:
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

"Sige" wrote:

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



  #5   Report Post  
Old December 2nd 05, 01:56 PM posted to microsoft.public.excel.worksheet.functions
Sige
 
Posts: n/a
Default Dynamic Ranges: Speed Issue

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



  #6   Report Post  
Old December 12th 05, 10:28 PM posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default Dynamic Ranges: Speed Issue

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.

Sige wrote:
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
dependent drop down boxes and dynamic ranges philcud Excel Worksheet Functions 2 September 1st 05 01:03 PM
Indirect and dynamic ranges Sam Excel Worksheet Functions 3 January 24th 05 08:01 AM
dynamic ranges Sam Excel Worksheet Functions 2 January 21st 05 08:46 PM
Dynamic Formulas with Dynamic Ranges Ralph Howarth Excel Worksheet Functions 5 January 21st 05 09:44 AM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 04:21 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017