Bug 127831 - recalculate vs. random functions in Calc
Summary: recalculate vs. random functions in Calc
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
Whiteboard: target:7.0.0
: 128371 (view as bug list)
Depends on:
Blocks: 132586
  Show dependency treegraph
Reported: 2019-09-28 02:36 UTC by TBeholder
Modified: 2022-07-30 19:20 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.
Comment 12 Eike Rathke 2020-04-27 15:51:12 UTC
Taking a stab at this.
Comment 13 koukasio 2020-04-27 18:43:34 UTC
May the source be with you, Eike.
Comment 14 Commit Notification 2020-04-28 09:28:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":


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:

Affected users are encouraged to test the fix and report feedback.
Comment 15 Joachim Kreutzer 2020-04-28 17:43:55 UTC
THANK YOU. I'm so grateful. @Eike
Comment 16 koukasio 2020-05-02 14:18:46 UTC
I will keep testing the release. It seems to work perfectly so far. I am very grateful. Thanks @Eike .
Comment 17 Joachim Kreutzer 2020-05-03 07:35:38 UTC
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:

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.
Comment 18 koukasio 2020-05-22 21:14:07 UTC
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.
Comment 19 Eike Rathke 2020-05-25 15:59:08 UTC
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.
Comment 20 Ming Hua 2020-05-30 13:39:06 UTC
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.
Comment 21 Andreas Säger 2020-06-02 15:31:54 UTC
Changing RANDBETWEEN from regular to volatile will break existing documents.
Comment 22 Andreas Säger 2020-06-02 17:56:25 UTC
=ROUND(Min+RAND()*(Max-Min)) used to be the volatile version of RANDBETWEEN(Min;Max)
Comment 23 Eike Rathke 2020-06-03 11:04:09 UTC
(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.
Comment 24 Eike Rathke 2020-06-03 11:15:31 UTC
(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.