Named range of consistent random numbers

Question

Background

Following on from a question I asked a while ago about getting an array of different (but not necessarily unique) random numbers to which the answer was this:

=RANDBETWEEN(ROW(A1:A10)^0,10)

To get an array of 10 random numbers between 1 and 10

The Problem

If I create a named range (called "randArray") with the formula above I hoped I would be able to reference randArray a number of times and get the same set of random numbers. Granted, they would change each time I press F9 or update the worksheet -- but change together.

This is what I get instead, two completely different sets of random numbers

I'm not surprised by this behavior but how can I achieve this without using VBA and without putting the random numbers onto the worksheet?

---

If you're interested

This example is intended to be MCVE. In my actual case, I am using random numbers to estimate Pi. The user stipulates how many random points to apply and gets an accordingly accurate estimation. The problem arises because I also graph the points and when there are a small number of points it's very clear to see that the estimation and the graph don't represent the same dataset

---

Update

I have awarded the initial bounty to @Michael for providing an interesting and different solution. I am still looking for a complete solution which allows the user to stipulate how many random points to use, and although there might not be a perfect answer I'm still interested in any other possible solutions and more than happy to put up further bounties.

Thank you to everyone who has contributed so far.

Answer 1

It's not a great answer but considering the limitation of a volatile function, it is definitely a possible answer to use the IF formula with Volatile function and a Volatile variable placed somewhere in the worksheet.

I used the below formula to achieve the desired result

=IF(rngIsVolatile,randArray,A1:A10)

I set cell B12 as rngIsVolatile. I pasted the screenshots below to see it in working.

When rngIsVolatile is set to True, it picks up new values from randArray:

When rngIsVolatile is set to False, it picks up old values from A1:A10:

Answer 2

This solution generates 10 seemingly random numbers between 1 and 10 that persist for nearly 9 seconds at a time. This allows repeated calls of the same formula to return the same set of values in a single refresh.

You can modify the time frame if required. Shorter time periods allow for more frequent updates, but also slightly increase the extremely unlikely chance that some calls to the formula occur after the cutover point resulting in a 2nd set of 10 random numbers for subsequent calls.

Firstly, define an array "Primes" with 10 different prime numbers:

={157;163;167;173;179;181;191;193;197;199}

Then, define this formula that will return an array of 10 random numbers:

=MOD(ROUND(MOD(ROUND(NOW(),4)*70000,Primes),0),10)+1

Explanation:

We need to build our own random number generator that we can seed with the same value for an amount of time; long enough for the called formula to keep returning the same value.

Firstly, we create a seed: ROUND(NOW(),4) creates a new seed number every 0.0001 days = 8.64 seconds.

We can generate rough random numbers using the following formula:

Random = Seed * 7 mod Prime

https://cdsmith.wordpress.com/2011/10/10/build-your-own-simple-random-numbers/

Ideally, a sequence of random numbers is generated by taking input from the previous output, but we can't do that in a single function. So instead, this uses 10 different prime numbers, essentially starting 10 different random number generators. Now, this has less reliability at generating random numbers, but testing results further below shows it actually seems to do a pretty good job.

ROUND(NOW(),4)*70000 gets our seed up to an integer and multiplies by 7 at the same time

MOD(ROUND(NOW(),4)*70000,Prime) generates a sequence of 10 random numbers from 0 to the respective prime number

ROUND(MOD(ROUND(NOW(),4)*70000,Prime),0) is required to get us back to an integer because Excel seems to struggle with apply Mod to floating point numbers.

=MOD(ROUND(MOD(ROUND(NOW(),4)*70000,Prime),0),10)+1 takes just the value from the ones place (random number from 0 to 9) and shifts it to give us a random number from 1 to 10

Testing results:

I generated 500 lots of 10 random numbers (in columns instead of rows) for seed values incrementing by 0.0001 and counted the number of times each digit occurred for each prime number. You can see that each digit occurred nearly 500 times in total and that the distribution of each digit is nearly equal between each prime number. So, this may be adequate for your purposes.

Looking at the numbers generated in immediate succession you can see similarities between adjacent prime numbers, they're not exactly the same but they're pretty close in places, even if they're offset by a few rows. However, if the refresh is occurring at random intervals, you'll still get seemingly random numbers and this should be sufficient for your purposes. Otherwise, you can still apply this approach to a more complex random number generator or try a different mix of prime numbers that are further apart.

Update 1: Trying to find a way of being able to specify the number of random numbers generated without storing a list of primes.

Attempt 1: Using a single prime with an array of seeds:

=MOD(ROUND(MOD(ROUND(NOW()+ROW(OFFSET(INDIRECT("A1"),0,0,SampleSize))/10000,4)*70000,1013),0),10)+1

This does give you an even distribution, but it really is just repeating the exact same sequence of 10 numbers over and over. Any analysis of the sample would be identical to analysing =MOD(ROW(1:SampleSize),10)+1. I think you want more variation than that!

Attempt 2: Working on a 2-dimensional array that still uses 10 primes....

Update 2: Didn't work. It had terrible performance. A new answer has been submitted that takes a similar but different approach.

Answer 3

OK, here's a solution where users can specify the number of values in defined name SAMPLESIZE

=MOD(ROUND(MOD(ROUND(NOW()+ROW(OFFSET(INDIRECT("A1"),0,0,SampleSize)),4)*10000*163,1013),0)+ROUND(MOD(ROUND(NOW()+ROW(OFFSET(INDIRECT("A1"),0,0,SampleSize))*2,4)*10000*211,1013),0)+ROUND(MOD(ROUND(NOW()+ROW(OFFSET(INDIRECT("A1"),0,0,SampleSize))*3,4)*10000*17,1013),0)+ROUND(MOD(ROUND(NOW()+ROW(OFFSET(INDIRECT("A1"),0,0,SampleSize))*5,4)*10000*179,53),0)+ROUND(MOD(ROUND(NOW()+ROW(OFFSET(INDIRECT("A1"),0,0,SampleSize))*7,4)*10000*6101,1013),0),10)+1

It's a long formula, but has good efficiency and can be used in other functions. Attempts at a shorter formula resulted in unusably poor performance and arrays that for some reason couldn't be used in other functions.

This solution combines 5 different prime number generators to increase variety in the generated random numbers. Some arbitrary constants were introduced to try to reduce repeating patterns.

This has correct distribution and fairly good randomness. Repeated testing with a SampleSize of 10,000 resulted in frequencies of individual numbers varying between 960 and 1040 with no overall favoritism. However it seems to have the strange property of never generating the same number twice in a row!