generate numbers given probability of outcomes

[jerdjets]

Hi, I have a question about generating numbers. Is it possible in excel to generate numbers given probability of outcomes. For example, if the chance of the outcome being the number 1 is 20%, and the chance of the outcome being number 2 is 80%. I want excel to generate number 100 times, so I get number 1 about 20 times, and number 2 about 80 times.
THANKS

[jerdjets]

sorry i need to bump, anybody know how to do this?

[Bjornar]

You can use the =RAND() function. this returns a random number between 0 and 1

Combine with IF you can do this:

=IF(RAND()<0.2,1,2)

Copy this to 100 cells.

[jerdjets]

thank you i will try it

[jerdjets]

I am very sorry about not making myself clear in my first post, I got the logic behind =if(rand()<0.2,1,2),

But i am trying to generate 5 different numbers with different probability of outcome.

i tried using multiple if statements inside and if statement, for example I tried to generate 3 numbers using this formula,
=IF((RAND()>0.33),IF((RAND()>0.33),IF((RAND()<.33),1),2),3)
the function got too complicated, and Excel would change the values in my previous cells.
Is there an easier way to do this.
THanks

[Bjornar]

The "problem" with RAND() function is that it recalculates every time you do a change in any other cell. There is 2 ways around this. You can turn of automatic calculation, and calculate manually with F9, or you can turn the random numbers into static numbers.
The last alternative is the one I recomend for you. Generate the numbers. Copy the whole range. Past onto itself with Paste Spesial -> Values.

You can use nested IF statements but i think this only works up to 7 nested statements. I think VLOOKUP is a better choice, and easier to change the rules.

I have attached a file with both alternatives.