Generating numbers based on a probability distribution

**Delta223** · 03-28-2012, 06:35 AM

Hi,

I would like to generate a column of data based on the following probability distribution:

80 percent of cells = 1A
10 percent of cells = 2B
5 percent of cells = 3C
3.5 percent of cells = 4D
1.5 percent of cells = 5E

I suspect a formula can generate this distribution of numbers but am not sure

I have Excel 2010. Thanks for any help

**Søren Larsen** · 03-28-2012, 06:57 AM

You cand use =RAND() to generate random numbers between 0 and 1. Then use this formula, with the =RAND() function in column A starting from A1, =IF(A1>0.8;"1A";IF(A1>0.1;"2B";IF(A1>0.05;"3C";IF(A1>0.035;"4D";"5E"))))

Does it work? (Have not tested it)

In order for it to work, you have to have a long array with random numbers.

**Delta223** · 03-28-2012, 07:29 AM

Originally Posted by Søren Larsen

You cand use =RAND() to generate random numbers between 0 and 1. Then use this formula, with the =RAND() function in column A starting from A1, =IF(A1>0.8;"1A";IF(A1>0.1;"2B";IF(A1>0.05;"3C";IF(A1>0.035;"4D";"5E"))))

Does it work? (Have not tested it)

In order for it to work, you have to have a long array with random numbers.

It did not, an error popped up. Thanks for the help regardless

**Søren Larsen** · 03-28-2012, 07:30 AM

Originally Posted by Delta223

It did not, an error popped up. Thanks for the help regardless

Try replacing semicolons with commas.

**rounakbhasin** · 03-28-2012, 07:14 AM

Random.xlsx

This might be useful

**Delta223** · 03-28-2012, 08:07 AM

Hi, it works now but appears not to be in proportion:

http://i.imgur.com/uxb9e.png

There are far too many of 2B and 5E
(the others may also not be in proportion)