Random distribution of 1 and 0

[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

 

[Mike Middleton]

Alex -

If you want the formula to return, e.g., 68% chance of 0 and 32% chance of
1, use

=IF(RAND()<0.68,0,1)

- Mike
http://www.MikeMiddleton.com

 

[Bernard Liengme]

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

 

[Dave Peterson]

=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

 

[Gary''s Student]

In A1 enter:

=IF(RAND()<=0.68,0,1)
and copy down

 

[Joe User]

I want there to be i.e. 68% of 0 and 32% of 1.

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 -----

 

[helene and gabor]

if(randbetween(1,100) <69,0,1)

HTH

Gabor Sebo

 

[ozgrid.com]

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.

 

[Bernd P]

Hello Alexander,

I suggest to use my UDF ExactRandHistogrm():
http://sulprobil.com/html/exactrandhistogrm.html

Please note that this will need the UDF LRM()as well:
http://sulprobil.com/html/largest_remainder.html

Regards,
Bernd

 

[Alexander Meier]

Mike,

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

Best Regards,
Alex

 

[Dave Peterson]

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.

 

[Kent2046]

Sorry, testing only.
Bcos i do not know why my new post never appear after 2 days.