up vote 3 down vote favorite
share [g+] share [fb]

I have the following formula in Excel (not vba):

=RANDBETWEEN(1,10)

Then I auto fill down 10 rows. How can I re-write the formula so the numbers don't repeat.

link|improve this question
89% accept rate
2  
You can't. A random number means you can't predict what the next one will be - a random number in such a tight range (1-10) is likely to have a repeated value. This question is off-topic here, anyway, since it's "(not VBA)". Voting to move to superusers – Ken White Apr 29 '11 at 17:01
You may actually want a random sort? If you're generating the numbers randomly, you could get the same value multiple times, in a sample of this size. Or, you could make a check to see what numbers have already been selected; I think you'd need a macro for that (VBA?). – Piskvor Apr 29 '11 at 17:02
You may as well use "CELL" and get the rownumber for each row if you want to have 10 unique numbers over 10 rows... – gbn Apr 29 '11 at 17:04
1  
@Ken, I agree that this question should be move to SuperUser, but realize that it doesn't have to be VBA to be here on SO. You can have questions on worksheet-functions that are appropriate. – Lance Roberts Apr 29 '11 at 17:09
2  
@Ken, Excel worksheet-functions are very often programming-syntax and development problems. They can get quite complex. – Lance Roberts Apr 29 '11 at 17:34
show 3 more comments
feedback

migrated from stackoverflow.com Apr 30 '11 at 11:50

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 5 down vote accepted

There is a way to do it with worksheet functions only. However, you can't do it neatly in 10 cells. Instead you need 10 columns of ten cells. Do the following:

  1. Enter =RANDBETWEEN(1,10) in A1:A10.
  2. Enter =A1 in B1.
  3. Enter =IF(OR(A2=A$1:A1),MOD(A2,10)+1,A2) as an array formula in B2. (Enter formula by pressing Ctrl+Shift+Enter)
  4. Fill B2 down to B10.
  5. Fill B1:B10 over to J1:J10.

J1:J10 will be a random sequence of the numbers 1 to 10.

link|improve this answer
Before I get attacked, this might not be completely random, but I think it's the best approximation you can get with worksheet functions only. Ideally, instead of replacing the duplicate with MOD(duplicate,10)+1, you would replace it with another random number, but there's no guarantee that a finite number of iterations (i.e., columns) would resolve the problem. – Excellll Apr 29 '11 at 18:15
Hm, maybe it is essentially random. Chi-squared test of 1000 trials yielded p = 0.48. – Excellll Apr 29 '11 at 21:04
Since you are, in effect, using 10 random seeds (in A1:A10) to generate 10 numbers, the result should be just as random. Of course, the stipulation that no number is repeated in the final sequence reduces the randomness, to begin with. +1 – kaloyan Apr 29 '11 at 21:27
feedback
up vote 0 down vote

This previous answer does something similar: see how idx array is filled in the second example.

But it requires using VBA. I don't know if you want that or if you insist on using worksheet formulas only.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.