# Create list of random numbers without repeats? [closed]

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.

Thanks.

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

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

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

Sort by » oldest newest most voted

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

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.

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

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

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

C:\fakepath\16054271046113173.ods

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)

( 2020-11-16 06:51:17 +0200 )edit
1

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.

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