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
|