Other volatile functions (besides RAND and RANDBETWEEN)

edit

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!