Question regarding reducing Volatile Function recalculation, and "flagged" Volatile cells

[mrblister]

Hi all,

From what I understand, a volatile function causes a cell to recalculate, every time excels recalculates (any cell).
So I try to avoid volatile functions whenever I can, to reduce unnecessary recalculation. But sometimes I can't (or sometimes I'm just lazy) so I thought I could avoid recalculation by encasing volatile parts in IF statements. For example:

=IF(1<2,"Always True",[SomethingVolatile]), where [SomethingVolatile] could be RAND(), NOW(), INDIRECT(), OFFSET, etc

I THOUGHT that in the above example, because the volatile part will never run, then this cell won't recalculate (the volatile parts) every time Excel recalculates. Is that true or false? Or partially true? And if false, is there any way to (with formulas/functions/etc) reduce unnecessary recalculation of a formula that contains a volatile function?

Thanks!

 

[Jeffrey Mahoney]

I have never planned my workbooks based on whether the function is volatile or not. When I need it, I use it. Obviously I have to plan carefully because of the number functions that grab data from areas can cause great slow downs. Any formula that looks at another formula is going to get calculated itself regardless. I've been noticing that the new Array functions in 365 are pretty quick.

Are there ways to reduce the recalculation time? Tricks I've used:

If you don't need the values in a table updated regularly, Copy one row of formulas and put them in a hidden row above the table, value the cells in the table. When you need an update, copy the formulas down and value them again. I use macros for this.

Set the properties of a sheet to not calculate automatically: Sht.EnableCalculation = False. Or choose properties under the development tab and turn EnableCalculation to false

You can set your entire workbook to manual calculation and press F9 when you want the calc to happen

 

[MARK858]

@mrblister see Volatile Excel Functions -Decision Models for the first part of your question.

 

[joeu2004]

I try to avoid volatile functions whenever I can [....] I thought I could avoid recalculation by encasing volatile parts in IF statements. Is that true or false?

You are very wise to minimize, if not eliminate, the use of volatile functions. It is one of the most common reasons, together with whole-column references like A:A, for the complaint that Excel is non-responding or takes unduly long to recalculate.

Unfortunately, volatility is a characteristic of the formula, which is caused by the presence of a volatile function, not the execution of it.

Obviously, Excel cannot know if the volatile part of the formula should be executed without recalculating the formula. Excel is not "smart enough" to recognize conditional expresssions that are invariant -- an unusual programming choice, in general.

So, IF(FALSE,RAND()) does not avoid recalculating the expression.

However, if the conditional expression is false, the true-part is usually not re-evaluated. So the conditional expression is still useful, IMHO.

To confirm, create the following UDF in a normal VBA module (i.e. not a worksheet object).

Function myudf(x As Variant) As Variant
MsgBox "myudf: " & x
myudf = x
End Function

Then create the formula =IF(TRUE, myudf(1), myudf(RAND()))

Each time we enter a new value in another cell, we will see the MsgBox with "myudf 1".

But we should never see a MsgBox of the form "myudf 0.123456789012345", a random number.

This is also true of the CHOOSE function, which is especially fortunate. CHOOSE(1, myudf(1), myudf(2+RAND()), myudf(3+RAND())) executes only the first choice.

On the other hand, IIRC, the IFS function evaluates all of its parameters -- at least, all of the conditional expressions -- even though it returns the value of the first true parameter.

I don't know how I "know" that, since my version of Excel does not support IFS, and I don't believe I can test it with Excel Online. I presume that I asked someone else to test it.

You might test it yourself -- and post your findings in a response here. For example:

=IFS(myudf(1)=1, myudf(1.1), myudf(2)=2, myudf(2+RAND()), myudf(3)=4, myudf(3+RAND()), 1=2, myudf(4+RAND()))

The first and second conditions are intended to be true. Ideally, we would see only MsgBoxes with "myudf 1" and "myudf 1.1". But I suspect we will also see a MsgBox with "myudf 2"; and in the worst case, also "myudf 2.23456789012345", a random number.

The third and fourth conditions are intended to be false. I suspect that we will see a MsgBox with "myudf 3". In the worst case, we might also see "myudf 3.23456789012345" and "myudf 4.23456789012345", which are random numbers.