Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 8 down vote accepted

There is a an easy way to do this with two columns. In A1, enter

 =RAND()

and fill down to A10.

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