I was asked yesterday to enhance a spreadsheet to give a table of the top five-ranked items based on number of respondents to a question. With 115 possible respondents, there was a chance that there would be ties for some of the places, and I wanted the spreadsheet to override such ties with a random ordering. So if the most popular three question each attracted 85 responses, order them randomly into slots one, two and three nonetheless.
Excel's RANK function ranks all of these equal first. To get around this, I added a random, miniscule amount (=RAND()*0.00001) to the total number of responses and used this revised figure to determine the ranking. After creating the random numbers, I copied and pasted values over them to make sure the ordering didn't keep jumping with every action.
Not pure, but a creative way to solve the business problem, if I may say so.
