Please evaluate this weighted random number algorithm

[JenniferMurphy]

I would appreciate can comments on this method of generating weighted random numbers for the purpose of selecting elements of a range in inverse proportion to their values.

I the sheet below, there are 5 elements (Rows 7-11) representing the number of times each of 5 puzzles have been played. Column F shows the total number of games. There have been a total of 23 games played.

The weighting method is to take the inverse (Col H), raise that to a power (Col I), add 1 (Col J), then calculate the cumulative values (Col L). Columns M-P have the same calculations, but with a weighting factor of 2.00.

The UDF below generates a random number on [0,1), multiplies it by the maximum cumulative value (27.00 or 143), then searches for the first value that it is less than. Here's that code:

Public Function WtdRnd(pValues As Range, Optional pWt As Double = 1) As Long

Dim i As Long               'Loop index
Dim NumVals As Long         'Number of values in range
Dim MaxVal As Double        'Maximum value in range
Dim MaxCumVal As Double     'Maximum cumukative weighted value
Dim CumVal() As Double      'Cumulative totals
Dim Rnd01 As Double         'Initial random number on [0,1)
Dim RndCum As Double        'Random number scaled to MaxCumVal
NumVals = pValues.Count     'Get number of values in range
ReDim CumVal(0 To NumVals)  'Same size as range + (0)
CumVal(0) = 0               'Initialize 0 element to make loop work

MaxVal = WorksheetFunction.Max(pValues) 'Get the maximum value in the range

'Generate the weighted cumulative values
For i = 1 To NumVals
  CumVal(i) = CumVal(i - 1) + (((MaxVal - pValues(i)) ^ pWt) + 1)
Next i
MaxCumVal = CumVal(NumVals)

'Randomize                   'Generate a random seed
Rnd01 = Rnd()               'Get the random number on [0,1)
RndCum = Rnd01 * MaxCumVal  'Scale it to the maximum cumulative value

For i = 1 To NumVals          'Check RndCum against each of the weighted cumulative values
  If RndCum < CumVal(i) Then    'If it's this one,
    Exit For: End If              'Return it
Next i

WtdRnd = i

End Function

Is there a better way to do this?

 

[JenniferMurphy]

PS: I don't think I was as clear about the purpose as I should have been. Over time, I want the number of times each puzzle is played to be roughly equal. When a new puzzle is added, it starts off having been played just once. I want that new puzzle to be played more often than the ones that have been played many times, but I don't want to play it to the exclusion of the other puzzles. The algorithm above gives more weight to puzzles that have been played fewer times so that they get played more often, but not consecutively.

 

[Sulprobil]

You can define a total number of games by when all puzzles should have been played (statistically) equally often.
Since the 104 piece puzzle has been played 9 times already, you might aim for all puzzles to be played 10 times.
5 puzzles would mean a total of 50 games but 23 have been played already.
So you write down the puzzle with
15 pieces (10 minus 1 played) = 9 times down
24 pieces 8 times down
40 pieces 5 times down
60 pieces 4 times down
104 pieces once down,
put all 27 names into a bin and draw from there without putting back in.
If you do not like to be that harsh to the 104 pieces puzzle aim for all puzzles to be played 20 times each....