jeudi 16 mars 2017

Named range of consistent random numbers

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(Sheet1!$A$1:$A$10)^0,ROW(Sheet1!$A$1:$A$10)^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

enter image description here

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




Aucun commentaire:

Enregistrer un commentaire