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).

Code:

```
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.