Workbook slowdown.

[wickerman]

I have a workbook made up of worksheets containing source data, pivot tables and charts but it is now becoming very slow since I have added a worksheet with many formulas.

If I move that worksheet containing many formulas (20 columns by 150 rows) to a different workbook will that help speed both of them up? The formulas will obviously still by referring to the original workbook.

Or is it simply by using formulas it slows things down whatever you do and I will have to think of a clever way of using pivot tables instead? Are there any other things that could be slowing it down? Other workbooks I maintain are fine.

[DonkeyOte]

wickerman, it will depend largely on the formulae you're using...

you say 150 x 20 formulas ... ie 3000 calcs

If these 3000 calcs are are Array (CSE / Sumproduct) or Volatile (INDIRECT / OFFSET etc..) performance will be impacted significantly ... as is usually the case with better planning and use of helpers you will probably find you can streamline noticeably.

Can you provide an example of the formulae you have in operation ?

[broro183]

hi,

As DonkeyOte has suggested, people will be able to provide much more informed help if you provide an example file. However, if you want to expand your own understanding, you may find some useful information regarding the impact of certain formulae on calculation speed in the links on Charles William's below page:
http://decisionmodels.com/optspeed.htm

hth
Rob

[wickerman]

Thanks for your advice. The formulas all use SUMPRODUCTS so that would explain it. I thought my problems were solved with these formulas! I will have to have a rethink about how to manage these spreadsheets as originally the pivot tables didn't do what I wanted which is why I staretd using SUMPRODUCT.

In a seperate workbook I have a project that relies on merging cells to hold text and I now find that just merging or unmerging a cell or cells takes an age. The workbook on the whole has nothing like the number of formulas as the one mentioned above (certainly no SUMPRODUCTs) although a cell in it does refer back to that SUMPRODUCT heavy workbook. Would that be slowing it down simply by linking back to it?

[DonkeyOte]

Wickerman, in all honesty it would be guess work on our part... without question the Sumproducts are the issue in your other file... a PT approach may well be the answer depending on the nature of the aggregation, however, where PT's prove problematic a "helper cell" approach is worth investigating given this can alleviate the need for array formulae in the first instance ... the helper approach although less elegant is invariably more efficient than array, monumentally so in larger models.

[wickerman]

Before I tried combining formulas I would do calculations a section at a time spread over several cells so the idea of 'helper cells' is familiar to me (and I have a word for them now). I thought at the time it was bad practice to spread the calculation out but if it helps stop slowdown then thanks for pointing that out.

Thanks!

[DonkeyOte]

Not so much a case of spreading the calc - more along the lines of creating a unique key such that you can revert to non-array formulae ...

Consider the below:
(for sake of demo ignore PT, COUNTIFS (2007) etc... this is purely meant to demonstrate a concept!)

Col A: Name
Col B: Country
Col C: Continent
Col D: Values

Let's say you had 10000 rows of data and you wanted to sum the values in D for all people in Europe whose Country began with the letter E and whose Name began with the letter S ... entirely convoluted I grant you....

Traditional non-helper approach would be to use an array, normally a non-CSE array - ie Sumproduct:

=SUMPRODUCT(--(LEFT(A1:A10000,1)="S"),--(LEFT(B1:B10000,1)="E"),--(C1:C10000="Europe"),D1:D10000)

If you were doing lots and lots of these this would become an issue... you're referencing 40000 cells in each Sumproduct for a start...


However you could by introducing a formulae into E1:E10000 get rid of the need for the Sumproduct (array albeit non-CSE array), eg:

E1: =A1&":"&B1&":"&C1
copied down

Now you could revert to a non-array SUMIF formula utilising Wildcards... eg for the same result as before:

=SUMIF(E1:E10000,"S*:E*:Europe",D1:D10000)

Lots of the above type formula will be a LOT quicker than a Sumproduct approach.... I can't emphasise enough how much quicker !

[NBVC]

Good example, DonkeyOte.

Did you mean to add column D to the contenation?

Ie.. =A1&":"&B1&":"&C1&":"&D1 should be =A1&":"&B1&":"&C1 or else =SUMIF(E1:E10000,"S*:E*:Europe",D1:D10000) would not work.

[DonkeyOte]

NBVC, good spot, I've updated the example !

[wickerman]

Sounds good. I will try and post an example once I have the time to mock something up.

Thanks!