select random value in a range of cells

[Pete Morris]

I have a range of cells, eg (B10:B50) each cell containing
an integer.

How do I select a random integer that is in one of those cells?


I want a formula in cell so that that cell contains a random value
from that range.

 

[Ragdyer]

=INDEX(B10:B50,INT(RAND()*50)+1)

 

[Ragdyer]

Sorry ... forgot how to count!

Use this:

=INDEX(B10:B50,INT(RAND()*41)+1)

 

[Pete Morris]

Sorry ... forgot how to count!

Use this:

=INDEX(B10:B50,INT(RAND()*41)+1)

Can you explain the logic behind that?

Why 41?

When I use it in a different range it doesn't work properly.

 

[Ragdyer]

Here's a more simplified formula:

=INDEX(B10:B50,RAND()*42)

Now, the 42 is *1 more* then the size of the range (array), B10 to B50 which
contains 41 cells.

So, in a new range, if you had your specified numbers in A1 to A10, the
formula would be:

=INDEX(A1:A10,RAND()*11)

The Help files say that XL's Rand() function returns a random number which
is greater then or equal to 0, and *LESS* then 1, so *all* the possible
numbers are decimals!
I do believe that this is *not* true, since I cannot get Rand() to return
zero!
So, I would say that Rand returns numbers >0 and <1.

To convert these returns to larger numbers you just multiply the function.
However,
=Rand()*10
Will *NEVER* return 10, since 10 will *NEVER* be multiplied by 1.

Therefore, if your range contains 10 cells, and you want the possibility to
exist to return 10, simply multiply by the size of the range plus 1.

Now, the actual location of the range doesn't matter.
It could be A100 to A110.
When using Index(), the first cell of the *range* (A100) is always 1.
So this would work:
=Index(A100:A110,Rand()*12)