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

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
164
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,983
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top