Say there's a list of names:
Peter
Andrew
James
John
Philip
Thomas
Matthew
I want to select one name randomly, and the formulas I'm currently using to do this are =RANDBETWEEN(1,7)
and =VLOOKUP(A3,$A$6:$B$12,2)
.
However, is there a way to give each name a weight so that there is a higher chance for one particular name to be selected, because the only way I can think of doing that would be to add duplicate names to the list:
Peter
Peter
Peter
Peter
Peter
Andrew
Andrew
Andrew
James
James
John
Philip
Thomas
Thomas
Thomas
Thomas
Matthew
Mathhew
This way Peter would have the greatest probability of being randomly selected since the name appears the most, but I'd prefer not to do it this way if there's a more efficient way of doing this.
Any response is appreciated.
This method requires no additional columns or helper cells.
Instead of RANDBETWEEN(1,7)
use the following formula instead:
=CHOOSE(VLOOKUP(RANDBETWEEN(0,99),{0,1;28,2;45,3;55,4;60,5;65,6;87,7},2,1),1,2,3,4,5,6,7)
That will give you a weighting approximately equal to your longer list.
Then go ahead and use your =VLOOKUP(A3,$A$6:$B$12,2)
to return the name.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments