On 13/03/2012 12:34, JAgger1 wrote:
On Mar 13, 8:14 am, Martin
wrote:
On 13/03/2012 11:42, JAgger1 wrote:

I have a set of numbers in cell A1:J1

I use =INDEX($A1:$J1,ROUND(RAND()*COUNTA($A1:$J1),0)) in cell L1 to
N1 to get 3 random numbers from my set of numbers. Sometimes I end up
with ## instead of a random number in one of the cells? Anyone know
why? Thanks

There is a chance that ROUND(RAND()*COUNTA()) will be<0.5

And so rounds to 0 which is an invalid index

--
Regards,
Martin Brown

Hmm, so is there a way to write this so that wouldn't happen?

Someone else has alreay posted a suitable tweak.

Also, is it possible to modify the formula so there would be no
repeats?

If you mean by no repeats so that it behaves like drawing numbered balls
from a bag without replacement the short answer is NO, or at least doing
it would be so clumsy that it isn't worthwhile.

Simplest way to do that is have an InitMy_Random VBA function that
copies the list of possible values to a private array, shuffles them a
decent number of times by swapping a random pair of values and then
returns the shuffled array each time the My_Random() is called until
values run out when it should return #VALUE or some other "failed" flag.

--
Regards,
Martin Brown