4
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.
Mike
Posted 2011-04-29T16:58:12.927
Reputation: 297
Non identical random numbers
Answers
8
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.
Excellll
Posted 2011-04-29T16:58:12.927
Reputation: 11 857
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 – None – 2011-04-29T21:27:07.640
0
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.
Jean-François Corbett
Posted 2011-04-29T16:58:12.927
Reputation: 2 219
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 – 2011-04-29T17:01:25.053You 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 left the building – 2011-04-29T17:02:05.363
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 – 2011-04-29T17:04:51.077
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 – 2011-04-29T17:09:38.700
@Lance: How so? This site is for programming solutions. Excel formulas aren't programming; they're user-level functionality. If the formulas were being created programatically from macros written in VBA, they're programming. If it's about writing SQL, it's a programming question; if it's about how to install MySQL or SQL Server, it's not. Superusers is specifically designed to ask and answer user-level (non-programming related) software questions. – Ken White – 2011-04-29T17:17:43.753
2@Ken, Excel worksheet-functions are very often programming-syntax and development problems. They can get quite complex. – Lance Roberts – 2011-04-29T17:34:45.007
One Example, 2nd Example. – Lance Roberts – 2011-04-29T17:43:44.440
@Lance: There are probably exceptions to everything, if you look hard enough. I suppose you could find a way to justify a question about "What's the best brand of gas for my car?" if the poster said they use the car to commute to a consulting project where they write really complex kernel device drivers. Excel formulas are a user-level question, IMO. I guess we'll have to agree to disagree - don't vote to move questions you don't think should be moved, and I'll continue to vote to move those I think should be. :) – Ken White – 2011-04-29T20:55:45.383