Ask Your Question
0

Calc : function rand() side effect on cells values

asked 2016-03-17 12:21:20 +0200

topo gravatar image

updated 2016-03-17 14:10:43 +0200

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2016-03-17 12:43:19 +0200

karolus gravatar image

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> )

edit flag offensive delete link more

Comments

Hallo karolus,

Danke schön for your quick answer ! :)

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).

topo gravatar imagetopo ( 2016-03-17 13:45:38 +0200 )edit

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.

topo gravatar imagetopo ( 2016-03-18 09:32:19 +0200 )edit
0

answered 2016-03-17 12:30:13 +0200

topo gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-17 12:21:20 +0200

Seen: 1,738 times

Last updated: Mar 17 '16