Random Numbers and Me
 

Hey!

I´m having a little problem generating a few random numbers. The thing is
this: I have a huge list of people and I need to assign a random number
between 0.0 and 1.25 to each one.

The problem is I need the following:

70% of people to have a number between 1 and 1.2
20% of people to have a number greater than 1.2
10% of people to have a number below 1

Do you know how to do this???

Thanks!!

Random Numbers and Me
 

not another one
--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''s razor (Abbrev)


"John Pivot Table" wrote:

Hey!

I´m having a little problem generating a few random numbers. The thing is
this: I have a huge list of people and I need to assign a random number
between 0.0 and 1.25 to each one.

The problem is I need the following:

70% of people to have a number between 1 and 1.2
20% of people to have a number greater than 1.2
10% of people to have a number below 1

Do you know how to do this???

Thanks!!

Random Numbers and Me
 

Give the following macro a try; it will distribute the values in the three ranges randomly among the names (as opposed to assigning them to the same names each time as Mike's code does). In addition, Mike was correct about not always being able to apply the 70/20/10 rule perfectly (you would need the number of names to be a multiple of 10 for that to happen).... my code forces the 10% and 20% numbers (always rounded up at the percentage works out to a half a person or more) with the remainder of the count being approximately 70%). The only thing you need to do in the code is set the 3 Const values to their correct values for your particular layout (I assumed that the first name was in Row 2 with Row 1 being an assumed header; that the names were in Column A and the output column for the random numbers was Column B).

Sub Randoms()
Static RanBefore As Boolean
Dim TempElement As Single, RndNums() As Single
Dim X As Long, NameCount As Long, LastNamesRow As Long
Const FirstNamesRow As Long = 2
Const NamesColumn As String = "A"
Const RandNumColumn As String = "B"
' Make sure Randomize is executed only once per session
If Not RanBefore Then
RanBefore = True
Randomize
End If
LastNamesRow = Cells(Rows.Count, NamesColumn).End(xlUp).Row
NameCount = LastNamesRow - FirstNamesRow + 1
ReDim RndNums(1 To NameCount)
' Assign the number of random numbers for each range
For X = 1 To Format(0.1 * NameCount, "0")
RndNums(X) = Rnd
Next
For X = Format(0.1 * NameCount, "0") + 1 To Format(0.3 * NameCount, "0")
RndNums(X) = 0.15 * Rnd + 1.20001
Next
For X = Format(0.3 * NameCount, "0") + 1 To NameCount
RndNums(X) = 0.2 * Rnd + 1
Next
' Randomize those assignments within the array
For X = NameCount To 1 Step -1
RandomIndex = Int((X - LBound(RndNums) + 1) * Rnd + LBound(RndNums))
TempElement = RndNums(RandomIndex)
RndNums(RandomIndex) = RndNums(X)
RndNums(X) = TempElement
Next
' Assign those randomized numbers to the names
For X = 1 To NameCount
Cells(FirstNamesRow, RandNumColumn).Offset(X - 1) = RndNums(X)
Next
End Sub

--
Rick (MVP - Excel)


"John Pivot Table" wrote in message ...
Hey!

I´m having a little problem generating a few random numbers. The thing is
this: I have a huge list of people and I need to assign a random number
between 0.0 and 1.25 to each one.

The problem is I need the following:

70% of people to have a number between 1 and 1.2
20% of people to have a number greater than 1.2
10% of people to have a number below 1

Do you know how to do this???

Thanks!!

Random Numbers and Me
 

Hmmmm,

A very good point, my solution does assign random numbers to the same groups
each time. If i was to improve it i would populate a column with a random
number, do a sort and then apply my random numbers to the randomely sorted
list of names.
--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''s razor (Abbrev)


"Rick Rothstein" wrote:

Give the following macro a try; it will distribute the values in the three ranges randomly among the names (as opposed to assigning them to the same names each time as Mike's code does). In addition, Mike was correct about not always being able to apply the 70/20/10 rule perfectly (you would need the number of names to be a multiple of 10 for that to happen).... my code forces the 10% and 20% numbers (always rounded up at the percentage works out to a half a person or more) with the remainder of the count being approximately 70%). The only thing you need to do in the code is set the 3 Const values to their correct values for your particular layout (I assumed that the first name was in Row 2 with Row 1 being an assumed header; that the names were in Column A and the output column for the random numbers was Column B).

Sub Randoms()
Static RanBefore As Boolean
Dim TempElement As Single, RndNums() As Single
Dim X As Long, NameCount As Long, LastNamesRow As Long
Const FirstNamesRow As Long = 2
Const NamesColumn As String = "A"
Const RandNumColumn As String = "B"
' Make sure Randomize is executed only once per session
If Not RanBefore Then
RanBefore = True
Randomize
End If
LastNamesRow = Cells(Rows.Count, NamesColumn).End(xlUp).Row
NameCount = LastNamesRow - FirstNamesRow + 1
ReDim RndNums(1 To NameCount)
' Assign the number of random numbers for each range
For X = 1 To Format(0.1 * NameCount, "0")
RndNums(X) = Rnd
Next
For X = Format(0.1 * NameCount, "0") + 1 To Format(0.3 * NameCount, "0")
RndNums(X) = 0.15 * Rnd + 1.20001
Next
For X = Format(0.3 * NameCount, "0") + 1 To NameCount
RndNums(X) = 0.2 * Rnd + 1
Next
' Randomize those assignments within the array
For X = NameCount To 1 Step -1
RandomIndex = Int((X - LBound(RndNums) + 1) * Rnd + LBound(RndNums))
TempElement = RndNums(RandomIndex)
RndNums(RandomIndex) = RndNums(X)
RndNums(X) = TempElement
Next
' Assign those randomized numbers to the names
For X = 1 To NameCount
Cells(FirstNamesRow, RandNumColumn).Offset(X - 1) = RndNums(X)
Next
End Sub

--
Rick (MVP - Excel)


"John Pivot Table" wrote in message ...
Hey!

I´m having a little problem generating a few random numbers. The thing is
this: I have a huge list of people and I need to assign a random number
between 0.0 and 1.25 to each one.

The problem is I need the following:

70% of people to have a number between 1 and 1.2
20% of people to have a number greater than 1.2
10% of people to have a number below 1

Do you know how to do this???

Thanks!!
.