Rankings on First Come, First Serve

[daitegh]

I have this information in which applicants for a conference are ranking their preferences for example (red, yellow, orange). What formula can I write so that I give the person their first choice, but then when that area gets filled up with more than 1/3 of the people at that conference it shows their second choice? I want it to be a first come, first serve kinda thing so that people are motivated to sign up earlier. Thank you so much to anyone that helps!

 

[daitegh]

Nevermind I actually figured it out

 

[Stephen_IV]

Just curious! What formula did you use to solve it?

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-general/1272699-rankings-on-first-come-first-serve.html#post5104274
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[daitegh]

Yes on another forum somebody helped me with the formula

(https://www.mrexcel.com/forum/redir...gs-on-first-come-first-serve.html#post5104274)

but I changed the idea a bit bit because I wanted everyone who signed up to be assigned to something for the conference (even if it was their last choice) instead of a waitlist. So the capacities that are listed instead of numbers I created by adding up the total people listed with =Counta(B:B)-1 and then in the capacity box I put each of the three colors as =(1/3)*$A$16 (which was where the total was listed).