Random Numbers and Me

[John Pivot Table]

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!!

 

[Mike H]

Hi,

I think the 70/20/10 rule will be impossible to achieve exactly because (for
example) with 101 names how may are in each group bearing in mind were using
integers.

This should give a good approximation with your list of people on column A
of the active sheet

Sub randoms()
Dim FirstRange As Range
Dim SecondRange As Range
Dim ThirdRange As Range
Dim LastRow as Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set FirstRange = Range("A1:A" & Int(lastrow * 0.7))
Set SecondRange = Range("A" & Int((lastrow * 0.7)) + 1 & ":A" & Int(lastrow
* 0.9))
Set ThirdRange = Range("A" & Int((lastrow * 0.9) + 1) & ":A" & lastrow)

For Each c In FirstRange
c.Offset(, 1) = 1.2 * Rnd
Next

For Each c In SecondRange
c.Offset(, 1) = 1.25 - 1.21 * Rnd
Next

For Each c In ThirdRange
c.Offset(, 1) = 1 * Rnd
Next
End Sub
--
Mike

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

 

[Mike H]

oops,

Had my number ranges wrong, use this instead

Sub randoms()
Dim FirstRange As Range
Dim SecondRange As Range
Dim ThirdRange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set FirstRange = Range("A1:A" & Int(lastrow * 0.7))
Set SecondRange = Range("A" & Int((lastrow * 0.7)) + 1 & ":A" & Int(lastrow
* 0.9))
Set ThirdRange = Range("A" & Int((lastrow * 0.9) + 1) & ":A" & lastrow)

For Each c In FirstRange
c.Offset(, 1) = ((1.2 - 1) * Rnd + 1)
Next

For Each c In SecondRange
c.Offset(, 1) = ((1.25 - 1.2) * Rnd + 1.2)
Next

For Each c In ThirdRange
c.Offset(, 1) = 1 * Rnd
Next
End Sub


--
Mike

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

 

[Lars-Åke Aspelin]

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!!

You have already got a macro proposal for this.
If you prefer a worksheet function approach, here is one.

Assuming that your list of people, and nothing more, is in column A
starting from row 1.

In cell B1 put this formula:
=RAND()

In cell C1 put this formula:
=IF(ROW()<=COUNTA(A:A)*0.7,1+0.2*RAND(),IF(ROW()<=COUNTA(A:A)*0.9,1.2+0.05*RAND(),RAND()))

Copy cells B1:C1 down as far as you have data in column A.
Copy columns B:C and Paste Special to column D:E.
Sort columns A:E, sort by column D.
Delete columns B
You now have the requested random numbers in column B

Note: The procedure has to be repeated if you add more data to column
A so it is only efficient if your problem is a one time event.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

You have already got a macro proposal for this.
If you prefer a worksheet function approach, here is one.

Assuming that your list of people, and nothing more, is in column A
starting from row 1.

In cell B1 put this formula:
=RAND()

In cell C1 put this formula:
=IF(ROW()<=COUNTA(A:A)*0.7,1+0.2*RAND(),IF(ROW()<=COUNTA(A:A)*0.9,1.2+0.05*RAND(),RAND()))

Copy cells B1:C1 down as far as you have data in column A.
Copy columns B:C and Paste Special to column D:E.
Sort columns A:E, sort by column D.
Delete columns B
You now have the requested random numbers in column B

Note: The procedure has to be repeated if you add more data to column
A so it is only efficient if your problem is a one time event.

Hope this helps / Lars-Åke

Sorry, the sorting should just be on columns D:E, not A:E.

Lars-Åke