Ask Your Question

Function "RANDBETWEEN" recalculates on every Input

asked 2020-01-10 10:58:06 +0200

tlcrua gravatar image

updated 2020-01-10 11:24:31 +0200

Opaque gravatar image

Hi. Can you help me! I use "RANDBETWEEN" to create a random number. And I want to use this value until the value of only another 1 cell changed. But when I change value of any cell in sheet, the "RANDBETWEEN" works and change my value!

edit retag flag offensive close merge delete


Version: (x64)

tlcrua gravatar imagetlcrua ( 2020-01-10 11:00:38 +0200 )edit

Plese use a short descriptive title and put all the detail into details. It is pointless to have "title" and "details" to have the same text. Did that now - please keep in mind for future questions.

Opaque gravatar imageOpaque ( 2020-01-10 11:09:54 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2020-01-10 11:16:07 +0200

Opaque gravatar image

updated 2020-01-10 15:51:12 +0200


you can't change this behavior - RANDBETWEEN is a volatile function, which changes on every change in the sheet (on every input event). This is intended behavior. See also #tdf127831 - Comment 3

You could create your own version of a function in a macro and use that user defined function, to behave it like a non-volatile function.

Just an example [not fully elaborated]

Function name: RANDRANGE (change as you like)
Random range: 20 ... 500
Cell change triggering update: A1

Step 1: Create the following macro:

Dim oFunctionAccess As Object
Function RANDRANGE ( iStart as long, iEnd as long)

  oFunctionAccess = createunoservice("")
  RANDRANGE = oFunctionAccess.CallFunction("RANDBETWEEN",  Array(iStart,iEnd))

End Function

Step 2 Use the follwing formula:


Now change anything in the sheet and the value won't change. Change cell A1 and the value will be recalculated.

[Update] To avoid function access via unoservice you may also use:

Function MYRANDBETWEEN ( iStart as long, iEnd as long)
    MYRANDBETWEEN = iStart + int(rnd()*(iEnd - iStart))
End Function

Tested using LibreOffice:

Version:,Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US,Calc: threaded

Hope that helps.

If the answer helped you to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more


Behaviour was changed for versions V 6.2 and higher.

See also: tdf#102257 (in specific Comment #4 and the subsequent discussion).

Lupp gravatar imageLupp ( 2020-01-10 13:01:44 +0200 )edit

In tdf#102257, Comment 9:

menu Sheet -> Fill Cells -> Fill Random Number... and choose Distribution: Uniform Integer, Minimum: 10, Maximum: 20, OK.

The function will remain volatile.

LeroyG gravatar imageLeroyG ( 2020-07-27 02:16:54 +0200 )edit

Coming soon: New functions RAND.NV() and RANDBETWEEN.NV() which are non-volatile.

Lupp gravatar imageLupp ( 2020-07-27 10:19:43 +0200 )edit

answered 2020-01-15 17:55:35 +0200

tlcrua gravatar image

Thank you, Lupp! I have a little knowledge about this office so I can not do your guides. So I wait for a new version release. And I hope I can do your guides later. Thank you very much!

edit flag offensive delete link more


Please use the comments, and please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2020-01-16 00:25:27 +0200 )edit

answered 2020-07-27 10:25:37 +0200

Lupp gravatar image

updated 2020-07-27 10:34:11 +0200

RAND() always was volatile and RANDBETWEEN() was changed long ago to be volatile. No further change expected.

Coming soon (with V7.0): New functions RAND.NV() and RANDBETWEEN.NV() which are non-volatile.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-10 10:58:06 +0200

Seen: 610 times

Last updated: Jul 27 '20