Random Numbers and Me

  • Thread starter Thread starter John Pivot Table
  • Start date Start date
J

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!!
 
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)
 
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)
 
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!!
Click to expand...

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:D
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
 
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:D
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
Click to expand...

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

Lars-Åke
 
Back
Top