Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Numbers and Me
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) "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!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Numbers and Me
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) "Mike H" wrote: 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) "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!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Numbers and Me
On Thu, 14 Jan 2010 10:18:02 -0800, 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!! 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Numbers and Me
On Thu, 14 Jan 2010 22:27:59 +0100, Lars-Åke Aspelin
wrote: On Thu, 14 Jan 2010 10:18:02 -0800, 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!! 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 Sorry, the sorting should just be on columns D:E, not A:E. Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting numbers divisible by 4 from random numbers in ascending order | Excel Worksheet Functions | |||
How to select top six numbers from a of range of random numbers | Excel Worksheet Functions | |||
Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |