Hello!
I'm trying to create a weighted lottery with no duplicates to supplement a waitlist for enrollment at a private school.
The formula I'm currently using looks like this in the output column:
=INDEX(A$2:A$9,COUNTIF(C$2:C$9,"<="&RAND())+1)
This works well except for one problem - I need to ensure there are no duplicates in the output of random numbers.
Thank you for your help on this!
Welcome to the forum.
Please tell us your Office version in your forum profile as it may determine the solution offered.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you for your suggestions and welcome, Ali!
I've updated my Office version and attached my sample workbook to this post. If there is anything else I can do to make my question more clear or easily answerable, please let me know!
Thank you again,
Kiera
Here’s one way with new 365 functions:
=SORTBY(SEQUENCE(8),RANDARRAY(8))
https://www.excelcampus.com/tips/lis...no-duplicates/
Thank you, Ali.
Unfortunately, this formula doesn't seem to result in a sequence of numbers that has no duplicates that are simultaneously based on the weighted percentage as reflected in column B.
Any ideas?
You’ll need to explain your weightings.
Based on a family's status, I need to assign certain children a weight of 10%, 15% or 20% to increase their odds of being chosen. This weight is listed under column B, entitled Assigned %.
I created my original sheet based on the information I found on this page:
//exceljet.net/formula/random-number-weighted-probability
This is a low tech proposal that employs three helper columns which may be moved and/or hidden for aesthetic purposes.
1. Column F produces a list with duplicates and is populated using the formula from the exceljet article: =INDEX(A$2:A$9,MATCH(RAND(),C$2:C$9))
Note that the list formula may need to be copied down 100 rows or more to ensure the inclusion of all students.
2. Column D gives the first occurrence of each student in the list and is populated using: =MATCH(A2,F$2:F101,0)
3. Column E is populated using: =RANK.EQ(D2,D$2:D$9,1)
4. Finally the list of distinct student names is given in column H using: =INDEX(A$2:A$9,MATCH(ROWS(A$1:A1),E$2:E$9,0))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks