Ask Your Question
0

Calc : function rand() side effect on cells values [closed]

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

topo gravatar image

updated 2020-09-01 20:01:54 +0100

Alex Kemp gravatar image

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-01 20:02:46.840521

2 Answers

Sort by » oldest newest most voted
1

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

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

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

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

Question Tools

1 follower

Stats

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

Seen: 2,593 times

Last updated: Mar 17 '16