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 |
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 |
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. |
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 |
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 |
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. |
All times are GMT +1. The time now is 12:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com