Errata.... I wrote:
> 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.
|
