Volatile functions are recalculated on input events (from https://bugs.documentfoundation.org/show_bug.cgi?id=127831#c3).
Formulas starting with two equal signs are treated volatile (from the answer in Why two equal signs in formula ==DEC2HEX(L3,4)?).
Besides RAND() and RANDBETWEEN(), are there other volatile functions? Is there anything written to read about?
Thanks.
Volatile functions are:
Additionally any Add-In function that delivers a css::sheet::XVolatileResult return type.
Functions that are position-sensitive (i.e. recalculated upon move/insert/deletion):
No, INDEX() is not volatile. It just picks a cell or range from a predefined set of ranges.
NOW(), TODAY()
There are also functions like INDEX(), OFFSET() not able to exactly determine on what cells they depend without an evaluation. They may need to recalculate on many occasions. Probably they are also volatile.
Lookup functions and redirection like INDEX() and OFFSET() usually act on (possibly named) cell/range references, so they should not need to have that “volatile behavior”. When a cell range changes, references will update accordingly, which should be sufficient for triggering a recalculation.
There may be contexts where this does not apply, so they need recalc “always” anyway. IDK. I guess @Lupp has more experience than myself when it comes to this…
The INDIRECT() function is an exception for this type of redirection operation. It operates on a textual (possibly “hardcoded” in your formulas or data) representation of an address, which will not update when the range is altered. This makes me think that INDIRECT() needs to behave like a volatile function, which is one reason why I try to recommend the (slightly less intuitive) OFFSET() instead of INDIRECT() whenever possible.
I actually didn’t research it thoroughly to full depth, but since I built a lot of “speradsheet models” for this and that (partly just for fun), I can tell from my experience that all three functions you mentioned are treated as volatile. In the web I didn’t find explicit statements concerning LibO insofar, but there are lots of related complaints about “slow Excel” and respective explanations.
BTW: Concerning RAND() and RANBETWEEN() recently the non-volatile versions RAND.NV() and RANDBETWEEN.NV() were implemented.
@erAck might comment on this. I’m confident he knows for sure.
INDIRECT() needs to behave like a volatile function, which is one reason why I try to recommend the (slightly less intuitive) OFFSET() instead
OFFSET() is also volatile, as the ranges it would have to listen to may be altered upon each recalculation.
… ranges it would have to listen to may be altered …
Of course. Got it. Thanks!