How to force =RAND() to run only once in LibreOffice Calc?
-1
votes
1
answer
385
views
I am trying to randomize a list of words in LibreOffice Calc. Column A contains numbers 1 to 15, indicating the initial order of the words. Column B contains the words themselves. Here is the original data set(A1-B16):
No. Words
1 alike
2 bear
3 cane
4 dark
5 easy
6 fill
7 grass
8 hike
9 iron
10 joke
11 kilo
12 long
13 moan
14 nose
15 ouch
Then I wrote =RAND()
to C2-C16, which generates a random number between 0 and 1 that will be used to shuffle the list.
No. Words RNG
1 alike 0.865621083224796
2 bear 0.931833626420918
3 cane 0.227839223621858
4 dark 0.832537867713207
5 easy 0.969775037455717
6 fill 0.896303834396809
7 grass 0.432228403811195
8 hike 0.075830198639415
9 iron 0.867408144812027
10 joke 0.603695010104631
11 kilo 0.410994968775223
12 long 0.840853885814879
13 moan 0.919710215557461
14 nose 0.181802316389395
15 ouch 0.016758784630857
Then I selected A1-C16 and sorted in ascending order using RNG as the key. This is the result:
No. Words RNG
15 ouch 0.766843532872873
8 hike 0.947070486803519
14 nose 0.935710387458581
3 cane 0.907095084669842
11 kilo 0.998591593185233
7 grass 0.262657114008517
10 joke 0.508633980155267
4 dark 0.686797433391668
12 long 0.124417578504844
1 alike 0.316587339544393
9 iron 0.780943260363561
6 fill 0.020870056655958
13 moan 0.421603151053937
2 bear 0.017301889523655
5 easy 0.904484088935699
Note that Column C was recalculated while sorting, but the sorting was done according to the old values. So it does not look like the list was properly sorted when viewed by a 3rd party. I soon found out that they are regenerated every time I do any operation such as changing the font, font size, color, copying, pasting, etc.
This means that I cannot simply copy Column C to Column D. The original values are lost, and both Column C and Column D get new values.
No. Words RNG
15 ouch 0.53812335792605 0.043562592479011
8 hike 0.398881655918106 0.64370811632297
14 nose 0.201867181513088 0.755510523771107
3 cane 0.394920269465235 0.28416412724015
11 kilo 0.152969079524211 0.799996173130015
7 grass 0.902568202601681 0.663629657403564
10 joke 0.435628762542454 0.334338520877465
4 dark 0.096763206996108 0.886593020939989
12 long 0.385578268172482 0.379263717906069
1 alike 0.678912726335666 0.74399580956146
9 iron 0.838382502021304 0.609345470240727
6 fill 0.025112459308232 0.365339519963588
13 moan 0.321684809269134 0.92230175892848
2 bear 0.013301554887914 0.274360792567395
5 easy 0.308874453562179 0.640593438289194
I could just manually type the values on a separate column but then I can no longer prove that they were actually generated by RAND(). If I click the manually copied cells the expression is the number itself, not =RAND()
anymore. How can I create an expression or modify RAND's behavior so that RAND's values are fixed once generated?
Asked by wvz93427
(1 rep)
May 2, 2023, 05:15 AM
Last activity: May 2, 2023, 07:57 AM
Last activity: May 2, 2023, 07:57 AM