Bug 133295 - OFFSET.NV and INDIRECT.NV non-volatile functions in Calc
Summary: OFFSET.NV and INDIRECT.NV non-volatile functions in Calc
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2020-05-22 21:03 UTC by koukasio
Modified: 2022-07-20 09:47 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

volatile_functions_test (16.67 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-22 21:06 UTC, koukasio

Note You need to log in before you can comment on or make changes to this bug.
Description koukasio 2020-05-22 21:03:45 UTC
While it is obvious to everyone that RAND() is volatile, many users are not aware that OFFSET() and INDIRECT() are volatile as well. This can absolutely kill the performance of a spreadsheet, without even realizing the reason why this is happening, due to recalculating everything.
This is a correct behaviour and the same behaviour happens on msoffice.
While an experienced user can get around this issue by using the INDEX() function which is non-volatile, this is not possible on all cases. In some of these cases the user may responsibly want to absolutely avoid the volatility.
Would you please create two functions OFFSET.NV() and INDIRECT.NV() ?
This would work just like the RAND.NV() and RANDBETWEEN.NV() functions introduced on lo7.0.0 .
This would be a great feature and it will also work as a reminder/informer that OFFSET() and INDIRECT() are volatile and should be avoided in case performance is important.
I am attaching a lo7.0.0 spreadsheet sample that shows how one can test whether a function is volatile. The volatile cells refresh with every change or by simple "recalculate", while the non-volatile ones with "recalculate hard" .

Actual Results:

Expected Results:

Reproducible: Always

User Profile Reset: No

Additional Info:
Comment 1 koukasio 2020-05-22 21:06:57 UTC
Created attachment 161171 [details]
Comment 2 m_a_riosv 2020-05-24 09:17:00 UTC

Even better if all volatile functions would have their equivalent as nonvolatile.
Comment 3 koukasio 2020-05-25 17:20:18 UTC
(In reply to Eike Rathke from comment #19 bug-127831)
> Given how OFFSET() and INDIRECT() work a non-volatile version doesn't make
> sense.

 I fully understand the implications of using a non-volatile version of OFFSET() and INDIRECT() .
 I cannot speak for other people, even though I am sure that a lot of them would find it useful. Once upon a time, I used to use those two functions and my spreadsheets would get unbearably sluggish. It took years for me to realise that the cause for the slowdown was the use of these functions, which were causing the needless recalculation of everything rooted to the corresponding cells. Having the functions available would also work in an educational manner.
 Speaking for myself (m.a.riosv seems to agree), OFFSET.NV and INDIRECT.NV make perfect sense and their absence has been a longtime frustration and wish, in the cases that replacing them with the INDEX() function is not possible. There are cases where the use of the non-volatile versions cause absolutely no implications, but instead offer solutions without having the downsides of the volatile versions. It would be nice to have the user informed and be responsible to make the decision.
Comment 4 gdimi 2020-05-26 05:42:59 UTC Comment hidden (me-too)
Comment 5 Mike Kaganski 2022-07-20 09:47:25 UTC
(In reply to Eike Rathke from bug 127831 comment #19)
> Given how OFFSET() and INDIRECT() work a non-volatile version doesn't make
> sense.

Eike: is it possible to add a way for a function implementation to add some range(s) to their dependency list explicitly, so that not only the direct references that parser may resolve are taken into account at "set dirty" stage, but also this explicit list?

That could enable implementation of OFFSET.NV / INDIRECT.NV variants, hopefully without a drawback of having stale values there?