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!
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.
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.
Behaviour was changed for versions V 6.2 and higher.
See also: tdf#102257 (in specific Comment #4 and the subsequent discussion).
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.
Coming soon: New functions RAND.NV() and RANDBETWEEN.NV() which are non-volatile.
See LibreOffice 7.0: Release Notes - The Document Foundation Wiki
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!
Please use the comments, and please, if the answer solves the question click .
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 LibreOffice 7.0: Release Notes - The Document Foundation Wiki