Is this command doing what I think? =INDEX(B1:B10),RANK(C1,C1:C10))

[jarrod.c]

=INDEX(B1:B10),RANK(C1,C1:C10))

This is my formula. I found this online when looking for a way to create a sort of raffle draw spreadsheet, which would randomly choose a name out of a list like randomly picking a name out of a hat. In B1-B10 are a list of names, and in C1-C10 are a list of numbers created by the rand() command.

Now I thought this list ranked all the names in B by the values in C, so that whatever Cell had the highest number in C column, it would find the adjacent name in B column and output that. However this isn't the case. So I'm not sure exactly what this formula is doing and whether or not it can be used for the raffle draw I'm trying to create as I worry it could actually be biased.

Any help?

[daddylonglegs]

That formula can be used, when dragged down a column, to order the 10 B1:B10 values randomly without repetition.

If you only want to pull a single name you don't really need =RAND() in column C you can just use RAND() in the formula, e.g.

=INDEX(B1:B10,RAND()*10+1)

[jarrod.c]

Thanks for the quick reply.

What if I have a list of more or less than 10 members? I in fact have it set up to be of variable size using another cell which will hold the total number of entrants. Say this value is in D1 will the formula become

=INDEX(B1:B10,RAND()*D1+1)

Would this work for more random amounts of entrants that aren't as round as 10.

[daddylonglegs]

Yes, but you will need to have the names in the first n cells, e.g. if D1 is 7 then that formula will randomly return one of the names from B1:B7. If you want you can dispense with D1 and just use COUNTA to count the names, i.e.

=INDEX(B1:B10,RAND()*COUNTA(B1:B10)+1)

You can extend B1:B10 range to any size you want