Description: 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: -
Created attachment 161171 [details] volatile_functions_test
+1 Even better if all volatile functions would have their equivalent as nonvolatile.
(In reply to Eike Rathke from comment #19 bug-127831) > Given how OFFSET() and INDIRECT() work a non-volatile version doesn't make > sense. https://bugs.documentfoundation.org/show_bug.cgi?id=127831#c19 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.
+1 performance is important in large calcs'
(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?