Description: 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. Actual Results: Values are recalculated even when a given cell or its dependencies didn't change. Expected Results: Values are recalculated when a cell or any of its dependencies change. Reproducible: Always User Profile Reset: No Additional Info:
> also generates when any cell is updated. reproducible with: Version: 6.2.7.1 (x64) Build-ID: 23edc44b61b830b7d749943e020e96f5a7df63bf CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE Calc: 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.
@Eike 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) > @Eike > 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? https://bugs.documentfoundation.org/show_bug.cgi?id=127831#c4 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.
@Eike, @Wolfgang Jäger, @koukasio Ok, I understand Eike in #c9. Yes, please Eike, add two new function randnv and randbetweennv. It's important to reintegrate older spreadsheets and prospective calc-spreadsheets. And, again, it would be a wonderful feature in camparison to other spreadsheets.
Taking a stab at this.
May the source be with you, Eike.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/716655820d69a0d6aaa2714cb4f12bae1aa2b862 Resolves: tdf#127831 implement RAND.NV() and RANDBETWEEN.NV() non-volatile It will be available in 7.0.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
THANK YOU. I'm so grateful. @Eike
I will keep testing the release. It seems to work perfectly so far. I am very grateful. Thanks @Eike .
The new functions RANDBETWEEN.NV works fine, even in a very complex spreadsheet. By the way, there is a non imporant difference between excel and calc: =RANDBETWEEN(-0,5; 1) excel has outputs {0, 1} and calc has outputs {-1, 0, 1}. It's ok for me, you even can't change the probabilities by float-numbers, whether in calc nor in excel. =RANDBETWEEN.NV(-0,5; 1) in calc also delivers outputs -1, 0, 1 in same probability. It's such a fine new function for libreoffice 7. @Eike, thank you very much.
https://bugs.documentfoundation.org/show_bug.cgi?id=133295 I have requested another enhancement for new OFFSET.NV() and INDIRECT.NV() functions. Please feel free to support it if you want it as much as I do.
Given how OFFSET() and INDIRECT() work a non-volatile version doesn't make sense. And please don't mix other topics into this bug. Thanks.
I was doing translation work for these two newly added functions, and naturally wanted to look at the existing translations for RAND() and RANDBETWEEN() as references. It took me quite a while to find translations for RANDBETWEEN() because while RANDBETWEEN.NV() is in sc/ directory, RANDBETWEEN() is in scaddins/ directory, and these two are separate translation modules on Weblate. Are the differences between sc/ and scaddins/ still meaningful, or it's just a historical artifact? Regardless, with the current arrangement a note to translators next to the strings for RANDBETWEEN.NV() will be appreciated.
Changing RANDBETWEEN from regular to volatile will break existing documents.
=ROUND(Min+RAND()*(Max-Min)) used to be the volatile version of RANDBETWEEN(Min;Max)
(In reply to Andreas Säger from comment #21) > Changing RANDBETWEEN from regular to volatile will break existing documents. It may have skipped your attention, but the change of RANDBETWEEN() to volatile was already some releases ago, hence these two new non-volatile functions are introduced now.
(In reply to Ming Hua from comment #20) > Are the differences between sc/ and scaddins/ still meaningful, or it's just > a historical artifact? Both.. these Excel functions were implemented as Add-Ins because they were Add-Ins in Excel as well back then, and for storing to the old binary Excel .xls format it makes a difference because the Add-In is automatically treated the way Excel expects it. > Regardless, with the current arrangement a note to > translators next to the strings for RANDBETWEEN.NV() will be appreciated. The Function Wizard's strings? Could only be done in the msgctxt fields if that allows arbitrary text.