Random distribution of 1 and 0

  • Thread starter Thread starter Alexander Meier
  • Start date Start date
A

Alexander Meier

Hi.

I'd like to create an array consisting of nothing but the randomly
distributed numbers 1 and 0.
Well, no problem:
ROUND(RAND();0)
and fill the space with this.

But here's the question: I want there to be i.e. 68% of 0 and 32% of 1.
Is there a possibility to do this with Excel? At least the programm has
to count the number of 0 and the one of 1 and correct randomly to the
requested ratio of 1 to 0.

Possible with Excel?

I'd be glad if there is someone who can help me with this.

Alex
 
In A1 I entered =RANDBETWEEN(1,100)
In B1 I entered =--(A1>67) to return 0 or 1
I counted the number of zeros with =COUNTIF(B1:B50,0)/COUNT(B1:B50)
As I did repeated recalculations, this seemed to average on 68%
Not exactly what you anted but any use?

Alternative
In A1:A32 enter 32 zeros and in A33:A50 enter 1's
In B1:B50 use macro from http://www.ozgrid.com/VBA/RandomNumbers.htm
to generate 50 unique random numbers from 1 to 50
In C1 enter =INDEX(A1:A50,B1) and copy down column to select zeros/ones
randomly


best wishes
 
=rand()>0.68
Will return True or False

So:
=--(rand()>0.68)
will return 1 or 0.

The first minus changes true to -1 and the second minus changes the -1 to 1
 
Alexander Meier said:
I want there to be i.e. 68% of 0 and 32% of 1.
Click to expand...

Please note:

=--(RAND()<=0.32)

copied down or across results in an array of 1s and 0s with a
__probability__ that 32% will be 1s.

But if you have performed an experiment with coin tosses, you know that even
though there is a 50% of heads, that does not mean that 50% of the coin
tosses will actually be heads.

If you want to ensure that 32% are 1s, do the following:

1. Fill in a range of cells so that 32% are 1s. The 1s and 0s can be
consecutive. For example, A1:A32 should be 1s, and A33:A100 should be 0s.

2. In an adjacent range (e.g. B1:B100), fill in the cells with the formula
=RAND().

Caveat: Since formulas that use RAND() are volatile and change every
time any cell in the workbook is edited(!), it probably would behoove you to
put the range with =RAND() off on the side, then
copy-and-paste-special-value that range into the range adjacent to the range
of 1s and 0s. For example, put =RAND() into X1:X100, then copy X1:X100 and
paste-special-value into B1:B100, assuming A1:A100 contains the 1s and 0s.

Do one of the following.

3. Select the two adjacent ranges (e.g. A1:B100) and sort based on the range
with random values (column B).

or

4. In a third parallel range (e.g. C1:C100), fill in the cells with the
formula =INDEX($A$1:$A$100,RANK(B1,$B$1:$B$100))


----- original message -----
 
RE: I want there to be i.e. 68% of 0 and 32% of 1.


You may as well NOT call this "random" and fill the array yourself manually
as you are loading the dice to predict the outcome.
 
Mike,

that works. Thank you! Also to the others: Thanks for the help. :)

Best Regards,
Alex
 
In xl2003 menus, you'd use Format|conditional formatting.

You can use "cell Value is" 1 and give it a nice format. Then add a second
criteria and do a similar conditional formatting for 0.
 
Back
Top