"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?

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.


-----
[1] "No harm done" means: it should not cause an Excel error. However, it
does skew the probability distribution toward A1. That is, it is more
likely to return A1.