Combining first come first served with random selection from a waiting list.

rorymacaskill1

New Member
Joined
Mar 13, 2017
Messages
6
I'm part of an organisation that is altering the way it manages a waiting list, and we have decided we want a way of picking n people per year to join. However, we don't want this done on a first-come-first-served basis, nor do we want an entirely randomised selection, rather a combination of the two where the probability of being picked at random increases linearly with increasing time on the waiting list i.e. someone that has been on the waiting list for longer will be more likely to be picked than someone who has recently joined, though some chance remains for someone new to the waiting list to be picked. Any suggestions on how to model this in Excel?

Fairly proficient user, so nothing off limits!

Thanks in advance,
Rory
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

rorymacaskill1

New Member
Joined
Mar 13, 2017
Messages
6
One extra bit of information (just to make it even more complicated!), we would like the chances of the person that has been on the waiting list the longest to be 5x as likely (or ideally p​ times, i.e. take the value from a cell) to be picked vs. the newest member.
 

rorymacaskill1

New Member
Joined
Mar 13, 2017
Messages
6
Hi @pvr928

It is more of a theoretical problem than a real-life one, and is actually for use in a similar report that going to look into modelling the effects of how different 'selection processes' affect will diversity etc. in the organisation, when there is a distinct correlation for various reasons between the time people join the waiting list and their characteristics.

I do appreciate the link though!

Rory
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,242
Office Version
  1. 365
Platform
  1. Windows
Perhaps:

C3: =C2+IFERROR(((B2-MIN(B$2:B$5))*N+MAX(B$2:B$5)-B2)/(MAX(B$2:B$5)-MIN(B$2:B$5)),1)
B8: =INDEX(A2:A5,MATCH(RAND(),C2:C5/C6)) Array entered

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Days waiting</td><td style="font-weight: bold;;">Cumulative weight</td><td style="font-weight: bold;text-align: center;;">N</td><td style="font-weight: bold;text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Mary</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Martha</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Bill</td><td style="text-align: right;;">15</td><td style="text-align: right;;">2.21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Bob</td><td style="text-align: right;;">100</td><td style="text-align: right;;">3.63</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8.63</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;;">Pick</td><td style=";">Bob</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />
 
Last edited:

Forum statistics

Threads
1,140,926
Messages
5,703,215
Members
421,282
Latest member
hogie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top