Bug 127831 - recalculate vs. random functions in Calc
Summary: recalculate vs. random functions in Calc
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium enhancement
Assignee: Not Assigned
: 128371 (view as bug list)
Depends on:
Reported: 2019-09-28 02:36 UTC by TBeholder
Modified: 2020-02-25 11:35 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description TBeholder 2019-09-28 02:36:07 UTC
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:
Comment 1 Oliver Brinzing 2019-09-28 13:11:00 UTC
> also generates when any cell is updated.

reproducible with:

Version: (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

but the change was intended:

Bug 102257 - CALC: Recalculate does not work with RANDBETWEEN
Comment 2 Xisco Faulí 2019-09-30 13:36:41 UTC
@Eike, I thought you might be interested in this issue...
Comment 3 Eike Rathke 2019-09-30 15:20:34 UTC
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.
Comment 4 koukasio 2019-09-30 19:15:14 UTC
@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.
Comment 5 Joachim Kreutzer 2019-10-01 18:07:35 UTC
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.
Comment 6 m.a.riosv 2019-10-25 16:31:39 UTC
*** Bug 128371 has been marked as a duplicate of this bug. ***
Comment 7 Wolfgang Jäger 2020-01-10 12:41:41 UTC
(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.)
Comment 8 Joachim Kreutzer 2020-01-10 13:02:05 UTC
@Eike, @Wolfgang Jäger

Yes, I would prefer to introduce an additional optional trigger parameter too.

Thank you for this idea.
Comment 9 Eike Rathke 2020-01-10 14:00:01 UTC
Optional parameters to standardized functions are a no-go, they create a hell of interoperability problems with older releases and other implementations.
Comment 10 koukasio 2020-01-17 20:07:06 UTC
@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.
Comment 11 Joachim Kreutzer 2020-02-25 11:35:13 UTC
@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.