Ask Your Question

Create list of random numbers without repeats? [closed]

asked 2020-11-15 08:58:57 +0200

appreciatethehelp gravatar image

updated 2020-11-16 07:03:48 +0200

I need a list of 210 random numbers in Calc, whether integers or otherwise doesn't matter, as long as none of the numbers repeat i.e there are no duplicate numbers in the list.

I read a tutorial that the RAND() function was the one to use to do this, and have used the function accordingly. However I have found repetitions of certain numbers in my results- how do I prevent this from happening? I must admit I have very little knowledge of how the randomisation process works...

Attached is a document containing two columns, 1 with the list of numbers generated using RAND(), the other confirming duplicates of the first list.


C:\fakepath\RandNum List.ods

EDIT: @PKG here is the referent list with highlighted duplicates that I mentioned: C:\fakepath\16054316544219107 (2).ods

@dscheikey Here is the referent screenshot I mentioned: C:\fakepath\Screen Shot 2020-11-16 at 6.55.13 PM.png

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by appreciatethehelp
close date 2020-11-29 06:31:16.667552


The pseudo-random-number generator works with the best known algorithm for general purposes which is mathematically proven to have an extremely long period (>10^6001). Working with integers basically,it requires that a conversion take place finally, and the standard numbers (IEEE 754 Double) used by Calc can "only" represent about 4.51E15 different values in the range from 0 through 1. In addition the arithmetic comparison for "=" does (for good reasons) accept a few different bits of low significance. Thus it isn't completely impossible to get repetitions reported by COUNTIF() for RAND() sequences. I never heard of a proven case, and wouldn't expect to ever get one.
Your example document always shows 210 unique results for me.
What version of LibO are you using?
Are you sure about your system and the LibO install?

Lupp gravatar imageLupp ( 2020-11-16 10:30:52 +0200 )edit

I think you "hit the nail on the head" with the Lib install; after installing new version (choosing to replace old version when prompted), I found no repeats of RAND numbers. Thanks- you saved me a great deal of time there I think.

appreciatethehelp gravatar imageappreciatethehelp ( 2020-11-29 06:25:04 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-11-15 10:14:28 +0200

PKG gravatar image

updated 2020-11-16 08:48:52 +0200

Column B contains 210 numbers that are not repeated.
(Is practically a lottery 210 out of 1000).


edit flag offensive delete link more


Unfortunately there are numerous duplicates in the list you posted (see red cells in columns I added in updated version of file, attached in OP)

appreciatethehelp gravatar imageappreciatethehelp ( 2020-11-16 06:51:17 +0200 )edit

The results are in the column B. The "random numbers" in the column A are helper cells only.

I just tried the attached 16054271046113173.ods file: there are not duplicates in the column B.

Zizi64 gravatar imageZizi64 ( 2020-11-16 07:37:03 +0200 )edit

answered 2020-11-15 13:17:53 +0200

dscheikey gravatar image

A second solution with a slightly simpler formula and a safety if two random numbers are equal. Admittedly this is a very rare case.

Create list of random numbers without repeats.ods

edit flag offensive delete link more


Unfortunately I am having trouble viewing the formula in your post, as per attached screenshot in OP. I think this is due to the RAND.NV() function only having been released in Libreoffice 7, whereas I am still running 6.

Also I read that RAND.NV() produces "non-volatile" random numbers i.e. they are not recalculated at new input events- does this mean they won't recalculate when F9/recalculate is selected? If so this will be unsuitable for my purposes, as I need the random number list to be "recalculatable" at will by disabling Autocalculate and then using Recalculate to refresh the list.

appreciatethehelp gravatar imageappreciatethehelp ( 2020-11-16 07:01:34 +0200 )edit

That exactly is the difference between RAND() and RAND.NV(). You could force a hard recalc with Shift+Ctrl+F9 that recalculates everything, including RAND.NV().

erAck gravatar imageerAck ( 2020-11-16 14:12:40 +0200 )edit

Question Tools



Asked: 2020-11-15 08:58:57 +0200

Seen: 267 times

Last updated: Nov 16 '20