Need to have control of the calculation to specific ranges

[Maria J-son]

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

 

[Udo]

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

 

[Charles Williams]

- 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

 

[Udo]

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

 

[Maria J-son]

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

 

[Maria J-son]

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

 

[Maria J-son]

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(NameRngA15A200="2005";ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));ROW(INDIRECT("1:"&ROWS(NameRngA15A200))));1));"";INDEX(NameRngF15F200;SMALL(IF(NameRngA15A200="2005";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]

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]

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"&Counts!B17+5)<>"";IF(NameRngA15A200>"2005";OFFSET(Sheet3!C4;2;1;Counts!B17;1)*INDIRECT("P6"&Counts!B17+5);"");""));"";IF(INDIRECT("P6"&Counts!B17+5)<>"";IF(NameRngA15A200>"2005";OFFSET(Sheet3!C4;2;1;Counts!B17;1)*INDIRECT("P6"&Counts!B17+5);"");""))



/Kind regards

 

[Charles Williams]

Hi Maria,

(Not the same array as earlier message)
=IF(ISERROR(IF(INDIRECT("P6"&Counts!B17+5)<>"";IF(NameRngA15A200>"2005";OFFSET(Sheet3!C4;2;1;Counts!B17;1)*INDIRECT("P6"&Counts!B17+5);"");""));"";IF(INDIRECT("P6"&Counts!B17+5)<>"";IF(NameRngA15A200>"2005";OFFSET(Sheet3!C4;2;1;Counts!B17;1)*INDIRECT("P6"&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