Ask Your Question
0

Other volatile functions (besides RAND and RANDBETWEEN)

asked 2020-08-06 21:06:59 +0100

LeroyG gravatar image

updated 2020-10-13 22:00:12 +0100

Volatile functions are recalculated on input events (from https://bugs.documentfoundation.org/s...).

Formulas starting with two equal signs are treated volatile (from the answer in https://ask.libreoffice.org/en/questi...).

Besides RAND() and RANDBETWEEN(), are there other volatile functions? Is there anything written to read about?

Thanks.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2020-10-14 18:13:28 +0100

erAck gravatar image

Volatile functions are:

  • RAND
  • RANDBETWEEN
  • TODAY
  • NOW
  • FORMULA
  • INFO
  • INDIRECT
  • OFFSET

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

  • COLUMN
  • ROW
  • CELL
edit flag offensive delete link more

Comments

No INDEX()?

I finded a bit of info at Recalculate.

LeroyG gravatar imageLeroyG ( 2020-10-14 18:39:26 +0100 )edit

No, INDEX() is not volatile. It just picks a cell or range from a predefined set of ranges.

erAck gravatar imageerAck ( 2020-10-14 19:04:52 +0100 )edit
0

answered 2020-10-13 22:31:53 +0100

Lupp gravatar image

updated 2020-10-13 22:33:44 +0100

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.

edit flag offensive delete link more

Comments

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.

keme gravatar imagekeme ( 2020-10-14 09:22:32 +0100 )edit

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.

Lupp gravatar imageLupp ( 2020-10-14 15:40:14 +0100 )edit
1

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.

erAck gravatar imageerAck ( 2020-10-14 18:12:04 +0100 )edit

... ranges it would have to listen to may be altered ...

Of course. Got it. Thanks!

keme gravatar imagekeme ( 2020-10-14 18:24:09 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-06 21:06:59 +0100

Seen: 53 times

Last updated: Oct 14 '20