0

I have a workbook that uses multiple functions.

On one specific sheet there is an absurd amount of calculations, these functions very rarely need to be updated. While on other sheets there are functions that need to be updated as I use the workbook.

The workbook is now slowing down greatly and I assume that it is because of this one worksheet that contains 90% of the functions used in the workbook.

Can I write a function in a cell in such a way that Excel will know that it will only need to recalculate it when I manually trigger a calculation?

I know I could do this easily in VBA but in this situation there is a condition that prevents me from using VBA.

Sample function:

=IF('Prd Clt Ind'!B11574<>'Dev Clt Ind'!B11574,"! P " &'Prd Clt Ind'!B11574 & " D " & 'Dev Clt Ind'!B11574, 'Dev Clt Ind'!B11574)

and

=COUNTIF(A11574:AN11574,"!*")

Mandelbrotter
  • 179

2 Answers2

2

It is my understanding that the OP is asking for a non VBA solution to selective recalculation i.e. pause recalculating a whole sheet or range of cells until the results become relevant. While this would be nice a feature I can see why it would be problematic. The coherency of the spreadsheet is as risk, mixing old and new data and possibly triggering endless circular calculations when unpausing. The existing feature of manual calculation (which is known to the OP) is the safest way to offer such a feature. This way all the data contains either old values or everything has new values (no grey areas allowed).

Bar this, the OP has three options:

  1. Tentatively disable cells by inducing an error i.e. delete a named range called selectivecalc (with value true) and change functions like this =if(selectivecalc, original cell function)

  2. Move the "paused" data to another workbook and link back to the original. While this workbook is open changes should be automatic. While it is closed, it should be paused. I think! I am not sure what happens when two workbooks are linked to each other, or when you hit refresh all with only one workbook open.

  3. Try to optimise the calculation of the exisitng functions, taking into account how Excel's automatic recalculation works and when it is triggered. The rest of the post is dedicated to this optimisation.

Some of the comments by @Scott hints to this. Excel builds a dependency tree for each and every cell, and only recalculates a cell if its parent node changed/recalculates. As an example, say A1 = 1, B1 = A1*100 and C1 = LOG(SQRT(SIN(-4)*PI()^3)). While C1 is CPU intensive, it is only dependent on itself and will only ever recalc if edited by the user. B1 is dependent on A1, and will auto calc if A1 changes. Now if C1 = LOG(SQRT(SIN(-4)*PI()^3))*RAND(), it becomes problematic, as C1 will recalc on each and every other cell change, as RAND() is volatile. In such a case it would be wise to rather keep C1 the way it was, and define D1 = C1 * RAND(). CPU intensive C1 will not recalc, but supply its constant value to D1 for volatile recalculation.

Now specific to your case, your IF function is not too slow in itself, contains a text comparison and is dependent only on two other cells (call them IND cells). A change in any of those two cells would trigger a recalc of this local IF with text comparison. The output is TEXT which isn't great, looking forward.

The countif formula I assume is counting the number of TRUE cases of the first IF formulas. It has to do 40 text wildcard comparisons - even if only one of those 40 cells has had their IND cells trigger a local recalc. You can already make this faster by only comparing the first character of this string:

=SUM((LEFT(A11574:AN11574)="!")+0)      ...type in cell, press CTRL+SHIFT+ENTER (array formula)

There is probably a few of those COUNTIFs thats being trigger at once by a single cell change. If you can think of a way to reduce that to a binary compare and sum (not text), you will be flying.

Also, "Save as" > "Save as type" > "Excel Binary Workbook *.xlsb" might help, at least in reducing saving and loading times.

ExcelEverything
  • 3,194
0

I am not 100% sure this will work and it is difficult to test, but you could try wrapping your functions in an additional if linked to a boolean cell on the same page. I am wondering whether the calculations would then only update when you change the boolean cell?

for example I tested this:

=IF($C$1 = 1, IF(INDIRECT("'Prd Clt Ind'!B" & ROW())<>INDIRECT("'Dev Clt Ind'!B" & ROW()),"! P " &INDIRECT("'Prd Clt Ind'!B" & ROW()) & " D " & INDIRECT("'Dev Clt Ind'!B" & ROW()), INDIRECT("'Dev Clt Ind'!B" & ROW())),"")

copied down 10,000 cells and with simple numbers in the named sheets and it all worked too quick to allow me to determine if there was a difference when I changed cells on the linked pages. I am wondering whether this formula would only have the bulk of it evaluated when the C1 cell changed to 1.. again it is difficult to determine.

I also thought this discussion might have some form of solution for you: Excel function that evaluates a string as if it were a formula?

but could not solve it, you might have some luck. The idea was to use evaluate pointing at the string of your formula, this can then be made volatile or not volatile using the tricks outlined in response 2.

Justin Doward
  • 1,020