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
  • 169
  • 1
  • 2
  • 7
  • 1
    Excel will calculate the formula only if the source data changes not every time, unless one is using volatile functions like: OFFSET,INDIRECT,TODAY,NOW and a few others. If one is using those functions in great abundance then yes the calcs will be slow. Instead look and see if there are ways to rewrite the functions to avoid using volatile functions. – Scott Craner Mar 26 '21 at 13:17
  • The function compares values on two sheets. Would the fact that the scope of the function is outside of its sheet matter? – Mandelbrotter Mar 26 '21 at 13:22
  • no. Can you post a example of the function in question. – Scott Craner Mar 26 '21 at 13:25
  • Another thing that may cause slow down is the overuse of Array formula that refer to larger cell ranges than the actual data set. But again without seeing some examples of the formula we cannot help, because no, there is no way to specify certain formula to only calc manually. It is either all or nothing. – Scott Craner Mar 26 '21 at 13:34
  • Okay the Issue is that you probably have a sheet full of COUNTIF() formula. All the `*IF(S)` formula are slow. And a sheet full will cause issues. At this point the only options are to write vba to do the whole on the push of a button or to turn off the automatic calc, which will effect the whole workbook. And calc when you want to. – Scott Craner Mar 26 '21 at 14:40
  • Another option would be to perform your heavy calculations on power query. That's not updated automatically. – Máté Juhász Mar 27 '21 at 08:20
  • @Mandelbrotter let me know what worked for you in the end. Was a great question! – ExcelEverything Mar 29 '21 at 22:31
  • @Mobus In the end I chose to use a 2nd workbook. I would paste updated data in the 2nd workbook where I had a sheet with the calculations in it. Then then I copied - pasted values into the original workbook (No linking required) – Mandelbrotter Mar 30 '21 at 14:36

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,114
  • 1
  • 4
  • 16
  • Another random thought, if the text values of your functions are changing all the time, and several of them at once, you actually loose a lot of time on screen updates. Try to condense the number of characters you are outputting to a cell, mark the text colour white or better yet hide the column all together. If not, make the column width very narrow or the text size very small. – ExcelEverything Mar 28 '21 at 04:30
  • Also be careful of conditional formatting (CF) in large workbooks! CF is volatile, forcing the formatting to be recalculated at each and every workbook change (not the cell formula thought, just the formatting condition. – ExcelEverything Mar 28 '21 at 04:42
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,030
  • 5
  • 12
  • Good idea, but I don't if Excel is smart enough (yet) to build an intercell dependency tree. A change in any of the IND cells would still trigger a recalc of the IF. The IF would resolve much quicker though. In turn this cell would trigger a recalc of the COUNTIF formula and there goes your savings. Also, I doubt if the user will be happy with a blank answer. Would be great if you could say `=IF($C$1 = 1, IF(...), keep previous value)`. Alas Excel doesn't keep a cell history. – ExcelEverything Mar 28 '21 at 04:21