Calc : function rand() side effect on cells values

I’m novice to LibreOffice and just done a fresh install on my ArchLinux system.
Here is the up-to-date version of LibreOffice i have :

Version: 5.0.5.2 Build ID: 5.0.5.2
Arch Linux build-1 Locale: en-US
(en_US.UTF-8)

Willing to learn the very basics of spreadsheets, i opened a new spreadsheet document, and starting using the rand() function within cells formulas. For example, i set one cell with =rand() formula, and then copy down the formula to the cells below, and then to the cells at the next column to make an array of random values.

What is surprising, and i want to understand is, when i modify one cell on this spreadsheet, whatever the cell is, all cells containing random values (with =rand() formula), are modified.

I would like to understand this behavior.

Thanks.

Hallo

RAND() is designed to be volatile… eg. it recalculate itself on any other calculation in the same sheet.
If you want to avoid this behavior use sth. like =RANDBETWEEN(1;10000)/10000 which returns roughly the same, but recalculates only on forced recalculation ( <ctrl><shift><F9> )

Hallo karolus,

Danke schön for your quick answer ! :slight_smile:

You put me on the right path, because i nearly thought it could be a bug, but thinking about it then thought i had missed something. Thanks for the replacement function, i will use it.
Now i will be more carefull about how functions are defined (volatile or not for example).

I could find this as the official documentation of LO Calc relative to mathematical functions, but it doesn’t make any reference to volatile property that you mentioned :

Mathematical Functions - LibreOffice Help

I would be great to read somewhere a more complete definition of rand() function.

Say for example, if i have for the A4:C8 cells, entered the formula “=rand()”, to randomize an array 3x5 of values, if i just set one cell elsewhere (E1 for example) on the spreadsheet with a value , it will affect all the values of the A4:C8 range of cells.

Say :

0.7208296846 0.9681026701 0.1170904948
0.4816458312 0.2473452087 0.2533566935
0.1701826189 0.4916606066 0.28557925
0.2569225257 0.6591033325 0.9558581608
0.9941591621 0.5651747689 0.6260274619

Become

0.9184627659 0.8902114319 0.3679129528 2
0.8269501603 0.7634500963 0.5624116682
0.217940301 0.4380287969 0.668961365
0.5994510901 0.7790492607 0.9111794242
0.6082804288 0.787962539 0.5868770138