Ask Your Question
0

Function "RANDBETWEEN" recalculates on every Input

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

tlcrua gravatar image

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

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

Comments

Version: 6.3.4.2 (x64)

tlcrua gravatar imagetlcrua ( 2020-01-10 11:00:38 +0100 )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 +0100 )edit

2 Answers

Sort by » oldest newest most voted
1

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

Opaque gravatar image

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

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.

edit flag offensive delete link more

Comments

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 +0100 )edit
0

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

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

Comments

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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 55 times

Last updated: Jan 15