Disabling auto-calculate for others?

**Iscariot** · 01-22-2009, 06:18 AM

Good morning!

I'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Work-related), so auto-calculate is casuing crashing, so what I've done with this sheet is disabled auto-calculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess).

My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?

Stuart

**DonkeyOte** · 01-22-2009, 07:36 AM

Yes, you can use VBA in the Workbook_Open event to enforce Application.Calculation to Manual, eg:

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub

However this may cause other users issues in other files etc so it's not advised really...and obviously is dependent on them enabling macros in the first instance....

I think to answer your question the key is to work out how to improve performance of your file... are you using lots of Volatile functions like INDIRECT,OFFSET,TODAY etc... are you using lots of expensive Array formulae such as CSE arrays { } or SUMPRODUCTS ?

**Iscariot** · 01-23-2009, 05:01 AM

There's alot of SUMPRODUCTS, I'd say the main breakdown page which essentially is the one that crashes them, has just under 300 of them, which I imagine is causing the issues. They're all repeats of this essentially:

=SUMPRODUCT(--(Work_Data!P2:P9827="No"),--(RIGHT(Work_Data!N2:N9827,6)="Category 2"))

There's two of those for each answer that will be shown, the one being shown decided by a checkbox, so that you can see the percentage with and without the optional grade.

Any ideas?

**DonkeyOte** · 01-23-2009, 05:14 AM

As I say quite reguarly... if you find you have lots of Sumproducts you're best served adopting another approach... normally one of:

a) Pivot Table 
b) Concatenation keys at Source

Often a) is not viable.

For b) what I mean is that you take your key criteria in the sumproduct and create a concatenation key of said values in a new column on your source sheet -- this in turn enables you to dispose of multi conditional arrays and replace with SUMIF / COUNTIF etc which are far more efficient.

If for ex we looked at Work_Data... I would say you want to create a key, let's say for args sake in Column Z) along the lines of:

Z2: =P2&":"&RIGHT(N2,10)
repeating for range Z3:Z9827

If you go back to your main breakdown page you can then change:

=SUMPRODUCT(--(Work_Data!P2:P9827="No"),--(RIGHT(Work_Data!N2:N9827,10)="Category 2"))
*should be 10 given "Category 2" = 10 characters in length - your ex. has 6 (will always return 0)

to something less "heavy" utilising your new key in Z, eg:

=COUNTIF(Work_Data!Z2:Z9827="No:Category2")

If you post up a sample of your summary sheet I can probably help you replace them... I do this quite regularly for people and you will find that performance will improve the fewer arrays you have...
I suspect in reality your concatenation key may need to be a little more elegant your search criteria in Countif/Sumif more sophisticated as a result.

As it is, 300 Sumproducts each of which referencing say 20000 cells ... well, it's not going to be great...

**broro183** · 01-23-2009, 08:10 AM

hi Stuart,

DonkeyOte has this under control but if you want to learn even more yourself, you can read up a little more regarding worksheet performance at the below link (+ other frames that have links near the top of the screen) on Charles Williams' website:
http://www.decisionmodels.com/optspeed.htm

hth
Rob

**Iscariot** · 01-26-2009, 07:47 AM

Please find attached 1x [Working Sample.xls]

Any help or advice you could give me Donkey would be invaluable, I've only just got back into Excel after a very long 7yr absence, so wrapping my head around things again is somewhat exasperating.

Stuart

**DonkeyOte** · 01-26-2009, 11:12 AM

Stuart,

Well... it's taken me some time to work through this model... next time I'd advise you un-hide all the relevant formulae etc as next person may not be so accommodating ;-)

Firstly I would just say that you may find it worthwhile doing a refresher on absolute & relative referencing... it must have taken you a while to put this together, utilising absolutes ($ references) & relatives would have made your life a lot easier -- XL help is pretty good tutorial on this.

Have a look and see if the approach implemented in the attached model speeds up your calculation issues.

In short it seemed that if one went across all of your various Sumproducts the following columns on CSD_Data sheet were important:

F,H,I,K,M,N,O,P,Q,R,S,T

On CSD_Data I created a concatenation string of the values in each of the listed columns (^ delimiter) in Column W
(for column Q I did a little extra tweak to take the value and assign to the appropriate bucket (Less than 24, 24 to 29 etc...))

Once that was done I then went back and re-wrote every Sumproduct formula you had in your workbook and replaced them with COUNTIF utilising the above concatenation approach -- using wildcard (*) for those values that were irrelevant in terms of determining the appropriate count.... so if for ex. for one count only columns H & N were important (of those concatenated) you'd have a COUNTIF along the lines of:

=COUNTIF(CSD_Data!$W$8:$W$8181,"*^"&H Criteria&"^*^*^*^"&[i]N Criteria&"^*^*^*^*^*")

So for columns F,I,K,M,O,P,Q,R,S & T we use * to imply the value in that field is not important.

The above approach using lengthy strings and wildcard's is still not a great approach (if I had the time (& inclination!) I'd be inclined to create more smaller concatenation strings and use appropriately), however, I'm intrigued to see if it speeds performance for you.

**Iscariot** · 01-27-2009, 05:34 AM

Donkey, Right now I'd have your children.

The sheet now takes, even with about 2000 entrys, about 3 seconds to calculate.

I cannot thank you enough for the help you've given me, I really can't. I'm definitly going to really sit down and go over absolute referencing again, because if I can do one tenth the stuff you can do then I'll be set.

Honestly, that is incredible and the effort you've put in is shocking. You ever decide to take up a Games Workshop game and need help with it, let me know and I'll make sure you never lose another game (I know, it's not much but it's all I know to perfection lol).

Thank you again.

Stuart