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.
Version: 6.3.4.2 (x64)
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.