Random Numbers

[John Pivot Table]

Hey!

A question about random numbers in excel. I have a huge list of people and I
need to assign to each one a random number between 0.0 and 1.25.

The problem is I need the following:

70% of the people to be between 1 and 1.2
20% of the people to be above 1.2
10% of people below 1

any way I can do this randomly???

thanks!

 

[Mike H]

see your other post
--
Mike

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

 

[Bernie Deitrick]

John PT,

How about a pseudo-random approach?

Let's say that your list of names is in column A, starting in A2, with a header cell in A1. In B2,
enter the formula

=RANDBETWEEN(1000001,1199999)/1000000

and copy down until you match 70 % of your names. Then use

=RANDBETWEEN(1200001,1249999)/1000000

and copy down to match the next 20%, and finish with

=RANDBETWEEN(1,999999)/1000000

and copy down to match the last of your list.

(OR use, in B2

=IF(ROW(A1)<=ROUND(0.1*(COUNTA(A:A)-1),0),RANDBETWEEN(1,999999)/1000000,IF(ROW(A1)<=ROUND(0.8*(COUNTA(A:A)-1),0),RANDBETWEEN(1000001,1199999)/1000000,RANDBETWEEN(1200001,1249999)/1000000))

and copy down the whole way......)

This will create evenly distributed numbers (without duplication) close to the limits that you
describe (70% between 1 and 1.2 - how close depends on your population count). Then in C2, enter the
formula

=RAND()

and copy down to match column B.

Then select columns B and C, copy and pastespecial values, then sort B and C based on column C, and
delete column C.

HTH,
Bernie
MS Excel MVP