Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

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

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

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

2 Answers 2

up vote 6 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.

EDIT (years after the fact...): There is a much easier way to do this with two columns. In the first column, enter

 =RAND()

and fill down as far as you need.

In the adjacent column, enter

=RANK(A1,$A$1:$A$10)

and fill down. There is a negligible chance that of the 10 random floating point decimals (15 digits accuracy, I think), any two numbers will be the same. Thus, RANK will effectively always generate a random ordering of values from 1 to 10.

share|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
    
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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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