This solution generates 10 seemingly random numbers from 1 to 10, which are stored for almost 9 seconds at a time. This allows you to repeat calls to the same formula to return the same set of values in one update.
If necessary, you can change the time frame. Shorter time periods allow more frequent updates, but also slightly increase the extremely unlikely probability that some formula calls appear after the intersection point, which will lead to a 2nd set of 10 random numbers for subsequent calls.
First define an array of "Primes" with 10 different primes:
={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, which we can sow with the same value for some time; long enough for the called formula to keep the same value.
First, 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 inputting input from the previous output, but we cannot do this in one function. Thus, instead, it uses 10 different primes, essentially starting 10 different random number generators. Now this has less reliability when generating random numbers, but the test results below show that it actually looks pretty good.
ROUND(NOW(),4)*70000 gets our seed 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 corresponding prime
ROUND(MOD(ROUND(NOW(),4)*70000,Prime),0) is required to return us to an integer, because Excel seems to be trying to use Mod for floating point numbers.
=MOD(ROUND(MOD(ROUND(NOW(),4)*70000,Prime),0),10)+1 takes only the value from one place (random number from 0 to 9) and shifts it to give us a random number from 1 to 10
Test results:
I produced 500 lots of 10 random numbers (in columns instead of rows) for initial values increasing by 0.0001, and counted the number of times each digit took place for each prime number. You can see that each digit occurred almost 500 times and that the distribution of each digit is almost equal between each prime number. Thus, this may be adequate for your purposes.
Looking at the numbers generated in the immediate sequence, you can see the similarities between adjacent primes, they are not exactly the same, but they are pretty close in places, even if they are offset by several lines. However, if the update occurs at random intervals, you will still receive random numbers, and this should be enough for your purposes. Otherwise, you can apply this approach to a more complex random number generator or try a different combination of primes that are further apart.
Update 1: Trying to find a way to indicate the number of random numbers generated without saving a list of primes.
Attempt 1: using a single simple 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 gives you an even distribution, but actually it just repeats the same sequence of 10 numbers over and over. Any analysis of the sample would be identical to the analysis =MOD(ROW(1:SampleSize),10)+1 . I think you want more options than this!
Attempt 2: Work on a two-dimensional array that still uses 10 primes ....
Update 2: Doesn't work. He had a terrible performance. A new answer was presented using a similar but different approach.