Assign Values with Weight Probability

[MCDForm44]

I'm trying to assign a value of 1-30 to 30 unique names but I need certain scores to occur more often than others. I need to run this formula repeatedly and the same name cannot receive the same number twice. Is this possible?

I'd like the top 10 names to be more likely to receive scores of 21-30, the middle 10 of 11-20 and the bottom 10 scores from 1-10.

Thanks

 

[iliace]

How do you determine what the "top 10" or "middle 10" names are?

Also, when you say, "assign a value of 1-30", do you mean integers, or any number in-between? If you only have 30 values, 30 names, and "the same number cannot receive the same [score] twice", then you can at best only assign one number to each name.

 

[MCDForm44]

I mean integers 1-30. I need scores assigned to names each month that are different than the prior month. I need to assign the names into groups that can only get integers in a certain range like <=10 or >=21. In the example below I would need the monthly scores generated.

Name	Month 1 Score	Month 2 Score	Month 3 Score
Bob	8	9	10
Abe	3	1	2
Jake	6	7	4
Dave	4	5	6
Mike	1	2	3
Donald	7	6	7
Steve	9	10	8
Joe	2	3	1
Harry	10	8	9
Donald	5	4	5

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[iliace]

Here is a macro that will generate a list of random numbers between n and m. The format is getRandList(n, m). I use Stephen Bullen's quicksort algorithm here.

Public Function getRandList(iLow As Long, iHigh As Long) As Variant
  Application.Volatile


  Dim v As Variant
  Dim iDim As Long
  Dim i As Long
  
  iDim = iHigh - iLow + 1
  
  ReDim v(1 To iDim, 1 To 2)
  
  For i = LBound(v, 1) To UBound(v, 1)
    v(i, 1) = iLow + i - LBound(v, 1)
    v(i, 2) = Rnd
  Next i
  
  Call procSort2D(v, "A", 2, LBound(v, 1), UBound(v, 1))
  
  getRandList = v
End Function




Sub procSort2D(ByRef avArray, sOrder As String, iKey As Integer, iLow1 As Integer, iHigh1 As Integer)


  On Error Resume Next
  
  'Dimension variables
  Dim iLow2 As Integer, iHigh2 As Integer, i As Integer
  Dim vItem1, vItem2 As Variant
  
  'Set new extremes to old extremes
  iLow2 = iLow1
  iHigh2 = iHigh1
  
  'Get value of array item in middle of new extremes
  vItem1 = avArray((iLow1 + iHigh1) \ 2, iKey)
  
  'Loop for all the items in the array between the extremes
  While iLow2 < iHigh2
      
      If sOrder = "A" Then
          'Find the first item that is greater than the mid-point item
          While avArray(iLow2, iKey) < vItem1 And iLow2 < iHigh1
              iLow2 = iLow2 + 1
          Wend
  
          'Find the last item that is less than the mid-point item
          While avArray(iHigh2, iKey) > vItem1 And iHigh2 > iLow1
              iHigh2 = iHigh2 - 1
          Wend
      Else
          'Find the first item that is less than the mid-point item
          While avArray(iLow2, iKey) > vItem1 And iLow2 < iHigh1
              iLow2 = iLow2 + 1
          Wend
  
          'Find the last item that is greater than the mid-point item
          While avArray(iHigh2, iKey) < vItem1 And iHigh2 > iLow1
              iHigh2 = iHigh2 - 1
          Wend
      End If
  
      'If the two items are in the wrong order, swap the rows
      If iLow2 < iHigh2 Then
          For i = 1 To UBound(avArray, 2)
              vItem2 = avArray(iLow2, i)
              avArray(iLow2, i) = avArray(iHigh2, i)
              avArray(iHigh2, i) = vItem2
          Next
      End If
  
      'If the pointers are not together, advance to the next item
      If iLow2 <= iHigh2 Then
          iLow2 = iLow2 + 1
          iHigh2 = iHigh2 - 1
      End If
  Wend
  
  'Recurse to sort the lower half of the extremes
  If iHigh2 > iLow1 Then procSort2D avArray, sOrder, iKey, iLow1, iHigh2
  
  'Recurse to sort the upper half of the extremes
  If iLow2 < iHigh1 Then procSort2D avArray, sOrder, iKey, iLow2, iHigh1


End Sub

So, in the worksheet, I use this as an array formula. Then, each next column is 1 more than the previous, up to the limit of 10. Here is an example:

Sheet1

	A	B	C	D
1	Name	Month 1 Score	Month 2 Score	Month 3 Score
2	Bob	4	5	6
3	Abe	2	3	4
4	Jake	5	6	7
5	Dave	9	10	1
6	Mike	1	2	3
7	Donald	10	1	2
8	Steve	8	9	10
9	Joe	6	7	8
10	Harry	3	4	5
11	Donald	7	8	9

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:96px;"><col style="width:96px;"><col style="width:96px;"></colgroup><tbody>
</tbody>

Worksheet Formulas

Cell Formula B2 {=getRandList(1,10)} C2 =IF(B2=10, 1, B2+1) D2 =IF(C2=10, 1, C2+1) <tbody> </tbody>

Array formula: Don't enter the { } manually, after you type the formula, hold CTRL+SHIFT and then press ENTER!

<tbody>
</tbody>