RAND() function error? ##

[JAgger1]

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

 

[Martin Brown]

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

 

[JAgger1]

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

And so rounds to 0 which is an invalid index

Hmm, so is there a way to write this so that wouldn't happen?
Also, is it possible to modify the formula so there would be no
repeats?

 

[joeu2004]

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?

I am not quite sure why that ever works as written; but it does. I would
expect a #REF error when ROUND(RAND()*COUNTA($A1:$J1),0) returns 2 or more
because that requests row 2 or more from a range that includes only 1 row.

Anyway, I believe the following is what you might want:

=INDEX($A1:$J1,1,ROUND(RAND()*COLUMNS($A1:$J1),0))

Of course, you could replace COLUMNS($A1:$J1) with 10 unless you anticipate
inserting columns between columns A and J.

If RAND() returns less than 0.05, the column number will be zero. No harm
done [1], since INDEX(A1:J1,1,0) is perfectly valid. That returns the
entire range A1:J1. But in this context, Excel will select the first cell
of the range, namely A1.

RAND() always returns less than 1. So ROUND(RAND()*COLUMNS($A1:$J1),0)
should never exceed 10, the number of columns in A1:J1.

 

[joeu2004]

Anyway, I believe the following is what you might want:
=INDEX($A1:$J1,1,ROUND(RAND()*COLUMNS($A1:$J1),0)) [....]
If RAND() returns less than 0.05, the column number will be zero. No harm
done [1], since INDEX(A1:J1,1,0) is perfectly valid. That returns the
entire range A1:J1. But in this context, Excel will select the first cell
of the range, namely A1.

My bad! INDEX(A1:J1,1,0) is indeed valid, and it does indeed return the
entire range A1:J1. But Excel selected A1 only because I put the formula in
a row below A1:J1, not in row as you are doing. Otherwise, Excel returns a
#VALUE error.

Your formula should be:

=INDEX($A1:$J1,1,MIN(COLUMNS($A1:$J1),INT(1+RAND()*COLUMNS($A1:$J1))))

The MIN function should not be necessary. However, there is a defect in INT
[1] that causes INT(x) to return the next larger integer(!).


-----
[1] I call it a defect because INT(x) should never return the next larger
integer by definition, and VBA Int returns the correct result.

For example, INT(1+(1-2^-53)*10) returns 11. Note that 1-2^-53 is the
largest number less than 1 than can be represented internally by Excel.
However, it is unclear whether your RAND function returns values that close
to 1. In XL2003 and XL2007, RAND cannot return a number so close to 1 that
the INT expression will "fail" (return an unexpected result). However, RAND
was redesigned for XL2010.

 

[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.

 

[JAgger1]

I use =INDEX($A1:$J1,ROUND(RAND()*COUNTA($A1:$J1),0)) in cell L1 to

Someone else has alreay posted a suitable tweak.


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- Hide quoted text -

- Show quoted text -

Thanks for all your help

I won't worry about the repeats right now, the formulas you've given
me will do what I need.

Thanks again