In 6.0 and 6.1 RANDBETWEEN(X,Y) generates a new value only when its own cell is recalculated (F9), while RAND() generates a new value when any cell is updated by F9 or even a value entered in any cell.
In 6.3 RANDBETWEEN(X,Y) also generates when any cell is updated.
Could this behavior be set in options, please?
It's not always convenient to randomize everything (and then recalculate everything downstream) all the time; explicit refresh allows to play with constants vs. a given set of random values for debugging formulae, and/or generate only for a specific part of the table.
Also, needless recalculation as such probably isn't good for performance on huge spreadsheets.
I occasionally defined small cell groups to be randomized, via constructs like A3«="Group 1"», B3 «=RANDBETWEEN(1,10)+0*LEN($A$3)». It's a roundabout way to do things, of course, but controllable, easy to paste around and transparent (thanks to cell highlight).
Steps to Reproduce:
1. Enter '=RANDBETWEEN(1,10)' into one cell, '=RAND()' into another.
2. Enter some value into third cell.
3. Recalculate (F9( the third cell.
Values are recalculated even when a given cell or its dependencies didn't change.
Values are recalculated when a cell or any of its dependencies change.
User Profile Reset: No
> also generates when any cell is updated.
Version: 126.96.36.199 (x64)
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win;
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
but the change was intended:
Bug 102257 - CALC: Recalculate does not work with RANDBETWEEN
@Eike, I thought you might be interested in this issue...
Will not change the behaviour, it's intentional that both RAND() and RANDBETWEEN() are volatile functions to be recalculated on input events. Introducing yet another calculation option isn't overly attractive either, it adds another dimension to the behavioural matrix and silently changes calculations in an implementation that doesn't have the option or where it is set differently. The only clean way out is to add new functions that are not volatile and return a new result only when the expression is recalculated anyway.
@Eike I would personally be really grateful if you could implement two new functions eg. randnv randbetweennv . I am having a serious problem here, to the point that I can't update until a solution is offered. I was filled with hope when you mentioned this "clean way out" . I am sure there will be many to appreciate this solution.
I would be very grateful too, when there where 2 new nonvolotile functions RANDNV() / ZufallszahlNV() and RANDBETWEENNV() / ZufallsbereichNV().
My pupils would have a real chance to implement easy learning-calc-apps like 'What's my number' etc. (and besides they are learning the speadsheet-functions for themselves). And for me it's an elegant way for my many learning apps in meudela.de (just find&replace).
It would be a real feature in comparisan to other spreadsheets.
Thank you very much.
*** Bug 128371 has been marked as a duplicate of this bug. ***
(In reply to Joachim Kreutzer from comment #5)
> I would be very grateful too, when there where 2 new nonvolotile functions
> RANDNV() / ZufallszahlNV() and RANDBETWEENNV() / ZufallsbereichNV().
Did you consider a "private" or local solution similar to what "Opaque" suggested in https://ask.libreoffice.org/en/question/224536
(I would prefer to introduce an additional optional trigger parameter.)
@Eike, @Wolfgang Jäger
Yes, I would prefer to introduce an additional optional trigger parameter too.
Thank you for this idea.
Optional parameters to standardized functions are a no-go, they create a hell of interoperability problems with older releases and other implementations.
@Eike, @Wolfgang Jäger, @Joachim Kreutzer,
Taking into account Eike's reply:
Is the solution above satisfying to you?
New randnv and randbetweennv functions .
If you are as concerned as I am, let's conclude to a responsible request in order to respect our dear developers effort.