Sample Header Ad - 728x90

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