Population of 1's and 0's

[zuehlkescott5]

Hey everyone, I have a problem I am working on in Excel ( I can do it in SAS, but I need to do it exclusively with Excel). I work for a company that is interested in response rates of sales campaigns and I have been put in charge of constructing confidence intervals for the response rate. What I am given is an estimate for the response rate and a population size. What I need is a way to generate a field of 1's and 0's with a size the same as the sample size, and the number of 1's is equal to the estimate of responders. Here's an example:


Say I want to test a campaign on 50,000 people and expect a response rate of 4%. Then I want to generate a field that has 50,000 numbers in it, but has only 4% of 50,000=2000 1's and the other 48,000 are 0's.

Is this possible in Excel and, if so, how do I do it?

[tlafferty]

Select cells A1:A2000. Type the number 1, then press CTRL + Enter. Select cells A2001:A50000. Type the number 0, then press CTRL + Enter. In cell B1 enter the formula:

Please Login or Register  to view this content.

Autofill this formula through B50000. Click any single cell and the B column, then click the sort button. Since the numbers are random, it will randomly distribute the 1s and 0s.

[zuehlkescott5]

That definitely works for the specific example. What I am looking for, and I apologize for not being clear about this before, is a way to input a specific population size into a cell, and then a response rate into another cell, and then from that populate the field of 1's and 0's.

[tlafferty]

This code is in the attached workbook:

Please Login or Register  to view this content.

[HSV]

Much faster over 50.000 rows.
Speed ​​depends on the rate.

Please Login or Register  to view this content.

[watersev]

hi, zuehlkescott5, please check attachment, input values in A2 and B2 and run code "test".

[tlafferty]

Hi Watersev and HSV - I may have misunderstood goal of the confidence intervals. Isn't that something for Monte Carlo style (randomized) simulation? If so, the 1s and 0s should appear in random order.

[HSV]

Hello Thomas,

If so, then .....

Please Login or Register  to view this content.