Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic range
I am using a range in a formula, but in order to shorten
the calculatioon time I would like for the range to be selected "dynamically" according to 2 criteria. a) Start the range at the first value that is equal to a given date b) End the range at the last value in the column The formula will be on one sheet and the range is on another within the same workbook. The first value used in the range should give me the row number I will be using for transposing that range in other columns. I tried to use Indirect/match wich gave me the row number, but I cannot integrate it into my formula which uses the sumproduct function. The formula looks like this: SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data!$K$sx:$K$e x=Csx). I want to replace the "sx" by the starting row in the range and "ex" by the ending row in the range. The Worksheet/Range that hold the criteria is Data! H10:H10000. Thanks for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic range
Hi Victor
to be honest I doubt you will gain any performance improvements. At least the start/end calculation has to search through the whole range and if you don't have that much SUMPRODUCT formulas this could even slow you down (INDIRECT is not that fast). But if you want to try try something like the following: 1. Get the row numbers (separate formulas in different cells). Condition is in cell X1 1.a. Starting row, in cell X2 =MATCH(X1,Data!H10:H10000,0)+9 1.b. ending row in cell X3: Enter the following array formula (entered with CTRL+SHIFT+ENTER =MAX(IF(Data!H10:H10000=X1,ROW(Data!H10:H10000)) 2. Now use the following SUMPRODUCT formula (not sure why you have used Bsx in your example as comparison value: =SUMPRODUCT((INDIRECT("Data!$J$" & X2 & ":$J$" & X3)=B1)*(INDIRECT("Data!$K$" & X2 & ":$K$" & X3)=C1)) But as said if you have only some SUMPRODUCT formulas this would not gain much performance. I would use the following instead: =SUMPRODUCT((Data!$J$10:$J$10000=B1)*(Data!$K$10:$ K$10000=C1)*(Data!$H$ 10:$H$10000=your_condition)) or =SUMPRODUCT(--(Data!$J$10:$J$10000=B1),--(Data!$K$10:$K$10000=C1),--(Da ta!$H$10:$H$10000=your_condition)) -- Regards Frank Kabel Frankfurt, Germany Victor wrote: I am using a range in a formula, but in order to shorten the calculatioon time I would like for the range to be selected "dynamically" according to 2 criteria. a) Start the range at the first value that is equal to a given date b) End the range at the last value in the column The formula will be on one sheet and the range is on another within the same workbook. The first value used in the range should give me the row number I will be using for transposing that range in other columns. I tried to use Indirect/match wich gave me the row number, but I cannot integrate it into my formula which uses the sumproduct function. The formula looks like this: SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data!$K$sx:$K$e x=Csx). I want to replace the "sx" by the starting row in the range and "ex" by the ending row in the range. The Worksheet/Range that hold the criteria is Data! H10:H10000. Thanks for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic range
You may be right I'll try your last advice.
Add a date condition in a sumproduct statement. But everytime I change anything in that sheet, I have to wait between 1 to 2 minutes for recalc, so what I did is I put the sheet on manual calc. -----Original Message----- Hi Victor to be honest I doubt you will gain any performance improvements. At least the start/end calculation has to search through the whole range and if you don't have that much SUMPRODUCT formulas this could even slow you down (INDIRECT is not that fast). But if you want to try try something like the following: 1. Get the row numbers (separate formulas in different cells). Condition is in cell X1 1.a. Starting row, in cell X2 =MATCH(X1,Data!H10:H10000,0)+9 1.b. ending row in cell X3: Enter the following array formula (entered with CTRL+SHIFT+ENTER =MAX(IF(Data!H10:H10000=X1,ROW(Data!H10:H10000) ) 2. Now use the following SUMPRODUCT formula (not sure why you have used Bsx in your example as comparison value: =SUMPRODUCT((INDIRECT("Data!$J$" & X2 & ":$J$" & X3)=B1)*(INDIRECT("Data!$K$" & X2 & ":$K$" & X3)=C1)) But as said if you have only some SUMPRODUCT formulas this would not gain much performance. I would use the following instead: =SUMPRODUCT((Data!$J$10:$J$10000=B1)*(Data! $K$10:$K$10000=C1)*(Data!$H$ 10:$H$10000=your_condition)) or =SUMPRODUCT(--(Data!$J$10:$J$10000=B1),--(Data! $K$10:$K$10000=C1),--(Da ta!$H$10:$H$10000=your_condition)) -- Regards Frank Kabel Frankfurt, Germany Victor wrote: I am using a range in a formula, but in order to shorten the calculatioon time I would like for the range to be selected "dynamically" according to 2 criteria. a) Start the range at the first value that is equal to a given date b) End the range at the last value in the column The formula will be on one sheet and the range is on another within the same workbook. The first value used in the range should give me the row number I will be using for transposing that range in other columns. I tried to use Indirect/match wich gave me the row number, but I cannot integrate it into my formula which uses the sumproduct function. The formula looks like this: SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data! $K$sx:$K$ex=Csx). I want to replace the "sx" by the starting row in the range and "ex" by the ending row in the range. The Worksheet/Range that hold the criteria is Data! H10:H10000. Thanks for any help. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic range
Hi
do you use a lot of these SUMPRODUCT statements. If yes you may consider if a pivot table might be of use. Also see the following site for some otimisation tips: http://www.decisionmodels.com/optspeed.htm -- Regards Frank Kabel Frankfurt, Germany Victor wrote: You may be right I'll try your last advice. Add a date condition in a sumproduct statement. But everytime I change anything in that sheet, I have to wait between 1 to 2 minutes for recalc, so what I did is I put the sheet on manual calc. -----Original Message----- Hi Victor to be honest I doubt you will gain any performance improvements. At least the start/end calculation has to search through the whole range and if you don't have that much SUMPRODUCT formulas this could even slow you down (INDIRECT is not that fast). But if you want to try try something like the following: 1. Get the row numbers (separate formulas in different cells). Condition is in cell X1 1.a. Starting row, in cell X2 =MATCH(X1,Data!H10:H10000,0)+9 1.b. ending row in cell X3: Enter the following array formula (entered with CTRL+SHIFT+ENTER =MAX(IF(Data!H10:H10000=X1,ROW(Data!H10:H10000)) 2. Now use the following SUMPRODUCT formula (not sure why you have used Bsx in your example as comparison value: =SUMPRODUCT((INDIRECT("Data!$J$" & X2 & ":$J$" & X3)=B1)*(INDIRECT("Data!$K$" & X2 & ":$K$" & X3)=C1)) But as said if you have only some SUMPRODUCT formulas this would not gain much performance. I would use the following instead: =SUMPRODUCT((Data!$J$10:$J$10000=B1)*(Data! $K$10:$K$10000=C1)*(Data!$H$ 10:$H$10000=your_condition)) or =SUMPRODUCT(--(Data!$J$10:$J$10000=B1),--(Data! $K$10:$K$10000=C1),--(Da ta!$H$10:$H$10000=your_condition)) -- Regards Frank Kabel Frankfurt, Germany Victor wrote: I am using a range in a formula, but in order to shorten the calculatioon time I would like for the range to be selected "dynamically" according to 2 criteria. a) Start the range at the first value that is equal to a given date b) End the range at the last value in the column The formula will be on one sheet and the range is on another within the same workbook. The first value used in the range should give me the row number I will be using for transposing that range in other columns. I tried to use Indirect/match wich gave me the row number, but I cannot integrate it into my formula which uses the sumproduct function. The formula looks like this: SUMPRODUCT((Data!$J$sx:$J$ex=Bsx)*(Data! $K$sx:$K$ex=Csx). I want to replace the "sx" by the starting row in the range and "ex" by the ending row in the range. The Worksheet/Range that hold the criteria is Data! H10:H10000. Thanks for any help. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range | Excel Worksheet Functions | |||
Dynamic range | Excel Discussion (Misc queries) | |||
Dynamic range | Excel Discussion (Misc queries) | |||
dynamic range name | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |