Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to have control of the calculation to specific ranges
Hi,
I take this from another thread since it developed to another direction. I have a lot of links and after a cell value is changed, it take far to many seconds afterwards for the calculation to be possible to use it. Is it possible to: A/ Inhibit the common Application.Calculate that run after every change? B/ Just calculate specific ranges in specific sheets after a worksheet_change? C/ Be sure to cover every aspect to swicht it on again when leaving the workbook, and then continue with "the inhibited way" when returning to the workbook? Please answer any of these, or all if possible. /Kind regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to have control of the calculation to specific ranges
Hi Maria,
a quick win for A and C would be to generate a macro which will be executed whenever you open the specific workbook. There, you could state Application.Calculation=xlCalculationManual Then the workbook will be re-calculated only after pressing F9 or after closing the workbook. Re. question B I do not have a quick answer available yet. Udo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to have control of the calculation to specific ranges
- if calculation takes 4 seconds then you need to switch to Manual
(Tools--Options--calculation) and press F9 whenever you want to calculate - if your array formulae are taking the calculation time (which would not be surprising), why not take a look at speeding them up? see http://www.decisionmodels.com/optspeedj.htm for some suggestions - if you really want to control calculation of specific ranges use Range.calculate but be wary of its quirks (which vary by Excel version) see http://www.decisionmodels.com/calcsecretsg.htm for details. Not sure this is the right solution for you. Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Maria J-son" wrote in message ... Hi, I take this from another thread since it developed to another direction. I have a lot of links and after a cell value is changed, it take far to many seconds afterwards for the calculation to be possible to use it. Is it possible to: A/ Inhibit the common Application.Calculate that run after every change? B/ Just calculate specific ranges in specific sheets after a worksheet_change? C/ Be sure to cover every aspect to swicht it on again when leaving the workbook, and then continue with "the inhibited way" when returning to the workbook? Please answer any of these, or all if possible. /Kind regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to have control of the calculation to specific ranges
Hi Maria,
just had some time to think about your part C. To only calculate to just the rows 3 to 5 write Worksheets("this_sheet").Rows(3-5).Calculate . Another possibility is to define a range and calculate this: Worksheets(This_sheet").Range("B3:F200").Calculate . Good luck Udo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Follow up question ...
Thank you for a fantastic answer!
After that, I'll barly dare to ask more - but if you know the answer by hand: you are right, the Range.Calculation has to many traps... Can you recalculate only the cells on the current activesheet- without all following links downstream? Then on activate another sheet "with links downstream" it will only recalulate etc. Maybe I only need to have some code in a "worksheet_activate" event in that case? Only in one sheet do I have chartobjects that demand a complete calculation of the whole workbook. /Thanks again "Charles Williams" skrev i meddelandet ... - if calculation takes 4 seconds then you need to switch to Manual (Tools--Options--calculation) and press F9 whenever you want to calculate - if your array formulae are taking the calculation time (which would not be surprising), why not take a look at speeding them up? see http://www.decisionmodels.com/optspeedj.htm for some suggestions - if you really want to control calculation of specific ranges use Range.calculate but be wary of its quirks (which vary by Excel version) see http://www.decisionmodels.com/calcsecretsg.htm for details. Not sure this is the right solution for you. Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Maria J-son" wrote in message ... Hi, I take this from another thread since it developed to another direction. I have a lot of links and after a cell value is changed, it take far to many seconds afterwards for the calculation to be possible to use it. Is it possible to: A/ Inhibit the common Application.Calculate that run after every change? B/ Just calculate specific ranges in specific sheets after a worksheet_change? C/ Be sure to cover every aspect to swicht it on again when leaving the workbook, and then continue with "the inhibited way" when returning to the workbook? Please answer any of these, or all if possible. /Kind regards |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to have control of the calculation to specific ranges
Hi Udo,
Yes, it probably would be best to be able to only have recalculation in the activesheet ... but how ... I surley want to have calculation on the cells you see on the sheet ... /Regards "Udo" skrev i meddelandet ups.com... Hi Maria, a quick win for A and C would be to generate a macro which will be executed whenever you open the specific workbook. There, you could state Application.Calculation=xlCalculationManual Then the workbook will be re-calculated only after pressing F9 or after closing the workbook. Re. question B I do not have a quick answer available yet. Udo |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice regarding this particular array problem?
Hi again Charles,
I have read your pages and think that I maybe actually can make the formulas faster. In Sheet1 I have the named ranges NameRngA15A200 and NameRngF15F200 (range as written in the name)where you can add and delete rows and input data up to 200 rows - Usually ONLY maybe 10 of these rows have data (but sometimes with empty rows between). There are like 8 columns of these named ranges, all checking what year it is (could be 4 different years= 2005, 2006, 2007 or 2008) In Sheet2 I have this array formula 200 rows down and 10 similar columns , the 9 to the right just linked in a array to the left column that contain this array: =IF(ISERR(INDEX(NameRngF15F200;SMALL(IF(NameRngA15 A200="2005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200) )));ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));1)); "";INDEX(NameRngF15F200;SMALL(IF(NameRngA15A200="2 005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));ROW (INDIRECT("1:"&ROWS(NameRngA15A2001)) I don't use advanced filer bacause I have four of these arrays , starting around C207,C410,C613 and C816 with some headlines and sums between. The arrays are the same, but with different years. I have understood that i should use OFFSET formulas as names and use COUNTA to know how many. Since there could occure empty lines in Sheet1, I used INDEX in this array to get them without these empty lines. I have problem to interprit your advices on the webbsite to this particular problem. How can I use OFFSET/COUNTA in this? How can I reduce the number of lines in the arrays in Sheet2 when I don't know how many rows there is used in Sheet? The output of four different years will be of different number of rows, dynamically. It would be very, very, very nice of you, if I could get some advices from you (or any other of cource) regarding this array formula. The input in sheet1 will be used very much and therefore it is critical that the calculation will take to long time. / Kindest regards "Charles Williams" skrev i meddelandet ... - if calculation takes 4 seconds then you need to switch to Manual (Tools--Options--calculation) and press F9 whenever you want to calculate - if your array formulae are taking the calculation time (which would not be surprising), why not take a look at speeding them up? see http://www.decisionmodels.com/optspeedj.htm for some suggestions - if you really want to control calculation of specific ranges use Range.calculate but be wary of its quirks (which vary by Excel version) see http://www.decisionmodels.com/calcsecretsg.htm for details. Not sure this is the right solution for you. Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Maria J-son" wrote in message ... Hi, I take this from another thread since it developed to another direction. I have a lot of links and after a cell value is changed, it take far to many seconds afterwards for the calculation to be possible to use it. Is it possible to: A/ Inhibit the common Application.Calculate that run after every change? B/ Just calculate specific ranges in specific sheets after a worksheet_change? C/ Be sure to cover every aspect to swicht it on again when leaving the workbook, and then continue with "the inhibited way" when returning to the workbook? Please answer any of these, or all if possible. /Kind regards |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice regarding this particular array problem?
Hi Maria,
see this page for info on dynamic ranges http://www.decisionmodels.com/optspeedf.htm the formula for the dynamic range would be something like this =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) but if you want a separate dynamic range for each year you could need to replace the anchor cell reference Sheet1!$A$1 with a formula that gets the start row of the year, and the count would need to count only rows for that year. If you have a column that contains the year number in the first row for that year you can find the row number using MATCH, something like =Match("2005",$A$1:$A$200,0) (this will find the first row containing 2005) so if you have four of these formulae in 4 cells somewhere you have the start row number for each year and the number of rows for that year is startyear2-startyear1 and so on. So then you can construct a dynamic range formula something like this which should return only the rows in the year =OFFSET(Sheet1!$A$1,startyear1-1,0,startyear2-startyear1,1) This says something like : start in A1 then Offset down to the start of year 1 then return the number of rows there are in that year Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Maria J-son" wrote in message ... Hi again Charles, I have read your pages and think that I maybe actually can make the formulas faster. In Sheet1 I have the named ranges NameRngA15A200 and NameRngF15F200 (range as written in the name)where you can add and delete rows and input data up to 200 rows - Usually ONLY maybe 10 of these rows have data (but sometimes with empty rows between). There are like 8 columns of these named ranges, all checking what year it is (could be 4 different years= 2005, 2006, 2007 or 2008) In Sheet2 I have this array formula 200 rows down and 10 similar columns , the 9 to the right just linked in a array to the left column that contain this array: =IF(ISERR(INDEX(NameRngF15F200;SMALL(IF(NameRngA15 A200="2005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200) )));ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));1)); "";INDEX(NameRngF15F200;SMALL(IF(NameRngA15A200="2 005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));ROW (INDIRECT("1:"&ROWS(NameRngA15A2001)) I don't use advanced filer bacause I have four of these arrays , starting around C207,C410,C613 and C816 with some headlines and sums between. The arrays are the same, but with different years. I have understood that i should use OFFSET formulas as names and use COUNTA to know how many. Since there could occure empty lines in Sheet1, I used INDEX in this array to get them without these empty lines. I have problem to interprit your advices on the webbsite to this particular problem. How can I use OFFSET/COUNTA in this? How can I reduce the number of lines in the arrays in Sheet2 when I don't know how many rows there is used in Sheet? The output of four different years will be of different number of rows, dynamically. It would be very, very, very nice of you, if I could get some advices from you (or any other of cource) regarding this array formula. The input in sheet1 will be used very much and therefore it is critical that the calculation will take to long time. / Kindest regards "Charles Williams" skrev i meddelandet ... - if calculation takes 4 seconds then you need to switch to Manual (Tools--Options--calculation) and press F9 whenever you want to calculate - if your array formulae are taking the calculation time (which would not be surprising), why not take a look at speeding them up? see http://www.decisionmodels.com/optspeedj.htm for some suggestions - if you really want to control calculation of specific ranges use Range.calculate but be wary of its quirks (which vary by Excel version) see http://www.decisionmodels.com/calcsecretsg.htm for details. Not sure this is the right solution for you. Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Maria J-son" wrote in message ... Hi, I take this from another thread since it developed to another direction. I have a lot of links and after a cell value is changed, it take far to many seconds afterwards for the calculation to be possible to use it. Is it possible to: A/ Inhibit the common Application.Calculate that run after every change? B/ Just calculate specific ranges in specific sheets after a worksheet_change? C/ Be sure to cover every aspect to swicht it on again when leaving the workbook, and then continue with "the inhibited way" when returning to the workbook? Please answer any of these, or all if possible. /Kind regards |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice regarding this particular array problem?
Hi Charles,
think I already made som changes like that but not enough, stil more work .... I bought your FastExcel Addin a couple of hours ago, I'll see If I get any help from that...Right now, I only found that the large arrays also is slow - something I already knew... In the output Sheet2 there is arrays with 200 rows in height (to be able to cover a possible max 200 rows usage in input sheet1), but only need to be as many rows that is used in "NameRngA15A200". I think I reduced some of the load (?), the number of ""\""\""\ - signs (after F9 is pressed when formula is selected) are now [rows of NameRngA15A200 minus Used cells], earlier there was [ always 200 rows minus Used cells] QUESTION: If I achieved to reduce the number of ""\""\""\ - signs i the array from 200 to 30, will_there_be a preformance increase ? Even while the formula still is a 200 row array? Or will ist still calculate everything? If the 200 row output array on Sheet2 still is working hard - can I somehow reduce the array row in a dynamic way maybe? Like in a VBA code on a worksheet_change in Input Sheet1 create the array formula with just as many rows as needed? Any other way to reduce the size of the output arrays? The formula: (Not the same array as earlier message) =IF(ISERROR(IF(INDIRECT("P6:P"&Counts!B17+5)<"";I F(NameRngA15A200"2005";OFFSET(Sheet3!C4;2;1;Count s!B17;1)*INDIRECT("P6:P"&Counts!B17+5);"");""));"" ;IF(INDIRECT("P6:P"&Counts!B17+5)<"";IF(NameRngA1 5A200"2005";OFFSET(Sheet3!C4;2;1;Counts!B17;1)*IN DIRECT("P6:P"&Counts!B17+5);"");"")) /Kind regards "Charles Williams" skrev i meddelandet ... Hi Maria, see this page for info on dynamic ranges http://www.decisionmodels.com/optspeedf.htm the formula for the dynamic range would be something like this =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) but if you want a separate dynamic range for each year you could need to replace the anchor cell reference Sheet1!$A$1 with a formula that gets the start row of the year, and the count would need to count only rows for that year. If you have a column that contains the year number in the first row for that year you can find the row number using MATCH, something like =Match("2005",$A$1:$A$200,0) (this will find the first row containing 2005) so if you have four of these formulae in 4 cells somewhere you have the start row number for each year and the number of rows for that year is startyear2-startyear1 and so on. So then you can construct a dynamic range formula something like this which should return only the rows in the year =OFFSET(Sheet1!$A$1,startyear1-1,0,startyear2-startyear1,1) This says something like : start in A1 then Offset down to the start of year 1 then return the number of rows there are in that year Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Maria J-son" wrote in message ... Hi again Charles, I have read your pages and think that I maybe actually can make the formulas faster. In Sheet1 I have the named ranges NameRngA15A200 and NameRngF15F200 (range as written in the name)where you can add and delete rows and input data up to 200 rows - Usually ONLY maybe 10 of these rows have data (but sometimes with empty rows between). There are like 8 columns of these named ranges, all checking what year it is (could be 4 different years= 2005, 2006, 2007 or 2008) In Sheet2 I have this array formula 200 rows down and 10 similar columns , the 9 to the right just linked in a array to the left column that contain this array: =IF(ISERR(INDEX(NameRngF15F200;SMALL(IF(NameRngA15 A200="2005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200) )));ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));1)); "";INDEX(NameRngF15F200;SMALL(IF(NameRngA15A200="2 005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));ROW (INDIRECT("1:"&ROWS(NameRngA15A2001)) I don't use advanced filer bacause I have four of these arrays , starting around C207,C410,C613 and C816 with some headlines and sums between. The arrays are the same, but with different years. I have understood that i should use OFFSET formulas as names and use COUNTA to know how many. Since there could occure empty lines in Sheet1, I used INDEX in this array to get them without these empty lines. I have problem to interprit your advices on the webbsite to this particular problem. How can I use OFFSET/COUNTA in this? How can I reduce the number of lines in the arrays in Sheet2 when I don't know how many rows there is used in Sheet? The output of four different years will be of different number of rows, dynamically. It would be very, very, very nice of you, if I could get some advices from you (or any other of cource) regarding this array formula. The input in sheet1 will be used very much and therefore it is critical that the calculation will take to long time. / Kindest regards "Charles Williams" skrev i meddelandet ... - if calculation takes 4 seconds then you need to switch to Manual (Tools--Options--calculation) and press F9 whenever you want to calculate - if your array formulae are taking the calculation time (which would not be surprising), why not take a look at speeding them up? see http://www.decisionmodels.com/optspeedj.htm for some suggestions - if you really want to control calculation of specific ranges use Range.calculate but be wary of its quirks (which vary by Excel version) see http://www.decisionmodels.com/calcsecretsg.htm for details. Not sure this is the right solution for you. Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Maria J-son" wrote in message ... Hi, I take this from another thread since it developed to another direction. I have a lot of links and after a cell value is changed, it take far to many seconds afterwards for the calculation to be possible to use it. Is it possible to: A/ Inhibit the common Application.Calculate that run after every change? B/ Just calculate specific ranges in specific sheets after a worksheet_change? C/ Be sure to cover every aspect to swicht it on again when leaving the workbook, and then continue with "the inhibited way" when returning to the workbook? Please answer any of these, or all if possible. /Kind regards |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice regarding this particular array problem?
Hi Maria,
(Not the same array as earlier message) =IF(ISERROR(IF(INDIRECT("P6:P"&Counts!B17+5)<"";I F(NameRngA15A200"2005";OFFSET(Sheet3!C4;2;1;Count s!B17;1)*INDIRECT("P6:P"&Counts!B17+5);"");""));"" ;IF(INDIRECT("P6:P"&Counts!B17+5)<"";IF(NameRngA1 5A200"2005";OFFSET(Sheet3!C4;2;1;Counts!B17;1)*IN DIRECT("P6:P"&Counts!B17+5);"");"")) not sure I understand what this formula is trying to do, but it looks too complicated. why not add a non-array formula helper column that says something like =IF(and(P6<"",A15"2005"),1,0) and use that in your array formula or something like (non-array formula helper column) =IF(and(P6<"",sheet3!C6*P6,0) and then =SUMIF(A15:A200,"2005",helpercolumn) both of these should be fast. If they are not fast enough and you still have array formula problems then try using dynamic ranges in the array formula. regards Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation for varying ranges | Excel Worksheet Functions | |||
How to control calculation of specific cells | Excel Worksheet Functions | |||
Better control of axis ranges | Charts and Charting in Excel | |||
How do I return a calculation based on several ranges? | Excel Worksheet Functions | |||
Control of named ranges | Excel Programming |