Sumproduct Dynamic Range - XL2003
in [General]
|
Prev: Move all page contents to another page - how?
Next: remove bullet from sheet in Excel, imported from Powerpoint
From: Nate on 5 May 2010 11:15 I have a sumproduct formula(s) that is calculating very slowly in my workbook. =SUMPRODUCT(((Data!$D$2:$D$10000=$E22)*(Data!$A$2:$A$10000='H-Code SS Report'!F$21)*(Data!$K$2:$K$10000))) I'd like to insert a dynamic range to improve performance, but haven't had any luck. Any suggestions would be greatly appreciated.
From: Paul C on 5 May 2010 12:40 Rather then try to dynamically do each range in your SUMPRODUCT formula, utilize dynamically defined range names. ie SUMPRODUCT(--(range1=2),range2) Debra Dalgleish's Contextures site has a good section on establishing dynamic names http://www.contextures.com/xlNames01.html#Dynamic One caution, make sure you tie each dynamic name to the same marker to keep them identical sizes or the SUMPRODUCT will error. -- If this helps, please remember to click yes. "Nate" wrote: > I have a sumproduct formula(s) that is calculating very slowly in my > workbook. > > =SUMPRODUCT(((Data!$D$2:$D$10000=$E22)*(Data!$A$2:$A$10000='H-Code SS > Report'!F$21)*(Data!$K$2:$K$10000))) > > I'd like to insert a dynamic range to improve performance, but haven't had > any luck. Any suggestions would be greatly appreciated.
From: Jim Thomlinson on 5 May 2010 13:16 That method of creating a dynamic named range uses offset which is volatile. That means that you are actually adding calculation overhead by doing this. That will make this even slower... (while that calculation will be faster it will be calculated much more often) While I have not tried this it should work. Instead of using offset as your function try =(Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))) Which uses index which is not volatile. This does create a dynamic named range. The only question I have is whether named ranges are volatile by default. perhpas someone else can settle that... -- HTH... Jim Thomlinson "Paul C" wrote: > Rather then try to dynamically do each range in your SUMPRODUCT formula, > utilize dynamically defined range names. > > ie SUMPRODUCT(--(range1=2),range2) > > Debra Dalgleish's Contextures site has a good section on establishing > dynamic names > > http://www.contextures.com/xlNames01.html#Dynamic > > One caution, make sure you tie each dynamic name to the same marker to keep > them identical sizes or the SUMPRODUCT will error. > -- > If this helps, please remember to click yes. > > > "Nate" wrote: > > > I have a sumproduct formula(s) that is calculating very slowly in my > > workbook. > > > > =SUMPRODUCT(((Data!$D$2:$D$10000=$E22)*(Data!$A$2:$A$10000='H-Code SS > > Report'!F$21)*(Data!$K$2:$K$10000))) > > > > I'd like to insert a dynamic range to improve performance, but haven't had > > any luck. Any suggestions would be greatly appreciated.
From: Roger Govier on 6 May 2010 03:55
Hi Jim I agree entirely. I always use INDEX to create Dynamic Ranges. Debra kindly put a tutorial I wrote about this on her site http://www.contextures.com/xlNames03.html -- Regards Roger Govier Jim Thomlinson wrote: > That method of creating a dynamic named range uses offset which is volatile. > That means that you are actually adding calculation overhead by doing this. > That will make this even slower... (while that calculation will be faster it > will be calculated much more often) > > While I have not tried this it should work. Instead of using offset as your > function try > > =(Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))) > > Which uses index which is not volatile. This does create a dynamic named > range. The only question I have is whether named ranges are volatile by > default. perhpas someone else can settle that... |