a1=RANDBETWEEN(1,3)
a2=IF(A1=1,RANDBETWEEN(2,3),IF(A1=3,RANDBETWEEN(1,2),CHOOSE(RANDBETWEEN(1,2),1,3)))
a3=6-SUM(A1:A2)
the formula above will create random numbers between 1 and 3 but the numbers will not repeat. I would like to increase the numbers into 7 random numbers (from 1 to 7) but no repeating. Please help. Thank you.
example:
a1 = 3
a2 = 1
a3 = 5
a4 = 2
a5 = 4
a6 = 7
a7 = 6
if I click on refresh, numbers will change randomly but not repeating.
Try like this
Fill A1:A7 with =RAND()
Then in B1 and filled down to B7 put
=RANK(A1,A$1:A$7)
In cells A1 insert this formula, and drag down:Formula:
Please Login or Register to view this content.
In cells B1 insert this formula, and drag down:Formula:Please Login or Register to view this content.
You will have a unique list of 7 numbers in column B.
Please click the * icon below if I have helped.
Jonmo1 - IT WORKS PERFECTLY!!!!! i understand the RAND part but can you please explained the RANK and why it is not repeating (if possible in plain english). Thank you
does RANK only work in ROW because I tried to do it in COLUMN and it does not work?
Rank simply ranking the 7 results of each Rand.
and Rand is EXTREMELY unlikely to produce a duplicate...and I mean EXTREMELY.
So if all 7 rand's are different numbers, then they have a rank, one is the largest #, another is the 2nd largest..etc..
The example posted IS working on a Column...
A1:A7 = column
A1:G1 = row..
Now, if you wanted it to work accross a row, just adjust the refereces, and put th $ next to column letters instead of Row#s.
I have 126 individuals that I must randomly rank.
I out them into a spread sheet and tried everything to rank them randomly without duplicates from 1-126.
Nothing is working. Help:
Jasonjroberts,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi, JasonjrobertsOriginally Posted by Jasonjroberts
Here is a formula I found in the book "Excel Gurus Gone Wild" and was originally created by the alias PGC01.
To all who provided an answer.... GREAT, you solved my problems too
Edwin
One thing, your list does random order, however, the last value on the list is always the largest number. If the highest number is 7 then 7 is last on the list. If 125 is the highest number then 125 is the last on the list. I have tried this across multiple columns and get this result every time.
Anyway to change that?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks