RAND() function error? ##
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 |
RAND() function error? ##
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 |
RAND() function error? ##
On Mar 13, 8:14*am, Martin Brown
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? Also, is it possible to modify the formula so there would be no repeats? |
RAND() function error? ##
"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. |
RAND() function error? ##
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. |
RAND() function error? ##
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 |
RAND() function error? ##
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- 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 |
All times are GMT +1. The time now is 01:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com