The interesting part, to me, was step nine:
9. I will press F9 five times to regenerate the random numbers.
=RAND() in a column and intended to sort based on that column. F9, you’ll recall, is the shortcut key to recalculate a worksheet. But why recalculate five times? Does that make the numbers more random?
The reason that it interests me is because I’m guilty of the same thing. A fantasy football league I ran a few years ago had a rule that called for a raffle if two or more teams wanted the same player. Each team was allotted a number of chances based on their ranking to give the lower ranked team a better chance. If the second ranked team and the seventh ranked team were in the raffle, the second ranked team would get two of the nine chances and the other team would get seven of the nine. I used a similar set up as John in that I used
=RAND() in a column and sorted. I only recalculated three times though, not five.
How many times should you recalculate? The answer is none. The number returned by
=RAND() is as random as it’s ever going to get when you enter the formula. John could have entered the formula in the first cell, filled the formula down the column, and sorted and the list would have been as random as Excel can muster.
Why isn’t that enough?