1 or 0 outcome based on probability?

[sashoy]

Hi
I have problem with using formula, so I think that you can help me.
so, I have created excel spreadsheet with 500 rows. In column A i have random Value of occurance on certain event (from 1% to 100%) Lets imagine that we have tennis match and the probability for player a to win is 29% (totally random). Now I want in column B based on possibility of occurance to give me result 1 (which will be win) or 0 (lost match).
I think that I was clear enough. If you have further questions please ask me.
Thanks for any help.

 

[pplstuff]

b1: =if(a1>.5,1,0) Is this what you're looking for?

 

[sashoy]

I`m afraid not.
I need a formula which will randomly gives me outcome based on probability of occurance in the column A.
Based on this formula, it will always give me outcome 1 if the probability is greater than 50% and 0 if lower.
I suppose that i will need to use Rand, Vlookup, Index or something.
anyway, thanks for your answer

 

[Asala42]

Sounds like you're playing with simulations.

If A2 contains a win percentage (say 29%), you could use the formula:

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

will give you a 1 29% of the time, and 0 the rest (on average for every calculation, at least over the long term)

 

[pplstuff]

=if(rand()>.5,1,0) ??? I guess I'm missing what you're looking for. This version will randomly decide winners and losers; however, on average, there will be 1/2 winners and 1/2 losers. Can you give an example of what you'd like to happen? Or, if there should be some sort of distribution of winners?

 

[T. Valko]

I`m afraid not.
I need a formula which will randomly gives me outcome based on probability of occurance in the column A.
Based on this formula, it will always give me outcome 1 if the probability is greater than 50% and 0 if lower.
I suppose that i will need to use Rand, Vlookup, Index or something.
anyway, thanks for your answer

If the probability of a win is 29% then:

=--(RAND()<=0.29)

 

[pplstuff]

will give you a 1 29% of the time, and 0 the rest

Good call.

 

[sashoy]

yes, I will explain more precisely!
so we have tennis match, the player A have possibility to win (random number) but just for example we will take 20%, so I want in column B to have outcome either 1 or 0, preferably 4 times 0 and once 1. so if i have a lot of rows then this will be equal.
Mine problem is that in A2 column I have probability of 69% and A3 probability of lets say 45%. so the outcome in B2, B3 can be either 1, 0, but on long run to stick on the probability.

 

[pplstuff]

Asala42's post will do that.

 

[sashoy]

Ok thanks to all who replay here, it seems that I have found solution based on your GREAT help!