Wednesday, January 8, 2014

Fair random number generator

When you listen to a list of tracks (assuming you have 100 sound tracks on your pen-drive or CD) in your car and apply the random mode, you will notice that you listen to a specific track more than once per 100 played tracks... Case 1

One day, I was in a training in soft skills and each one in the training was asked to present himself in front of others. So.. for justice and fair, the instructor suggested to assign numbers for the attendant people (13 guy) and use random number generator in Microsoft Excel to select people, so no one will refuse or show complaint. The funny thing was that I was selected for more than 4 times because of such a function and the same for other colleagues... Case 2

Based on the previous cases, this is just a simple VB Macro code for Excel to generate unique random numbers between 1 and 100. 

Sub FairRandom()

Dim final, i, j, top As Integer
last = 1    'Last written cell
bottom = 1    'Bottom value of random function
top = 100    'Top value of random function
For i = 1 To 100000    'For high number of iterations (infinity)
RN = Evaluate("RandBetween(bottom,top)")
For j = 1 To final    'check if the generated random number equals one of the written cells before
If Cells(j, 1) = RN Then
GoTo 1     'if it is duplicated, then don't waste time and check the rest of cells
End If
Next j
Cells(j, 2) = RN
last = last + 1     'Increment last
If last > top Then
GoTo 2
End If
Next i
End Sub

The code results are shown in the first matrix, while the Excel function RandBetween(1,100) results are shown in the second matrix (Highlighted cells in red are duplicated using conditional formatting).