Function "RANDBETWEEN" recalculates on every Input

edit

Hello,

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]

Assumptions:
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("com.sun.star.sheet.FunctionAccess")
  RANDRANGE = oFunctionAccess.CallFunction("RANDBETWEEN",  Array(iStart,iEnd))

End Function

Step 2 Use the follwing formula:

=IF(A1;RANDRANGE(20;500);RANDRANGE(20;500))

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: 6.3.4.2,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.

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

But:
Coming soon (with V7.0): New functions RAND.NV() and RANDBETWEEN.NV() which are non-volatile.
See https://wiki.documentfoundation.org/R...