# 1 or 0 outcome based on probability?

#### sashoy

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
b1: =if(a1>.5,1,0) Is this what you're looking for?

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.

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)

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

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.
If the probability of a win is 29% then:

=--(RAND()<=0.29)

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.

Asala42's post will do that.

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

Replies
5
Views
72
Replies
4
Views
196
Replies
9
Views
505
Replies
0
Views
301
Replies
2
Views
74

1,211,843
Messages
6,104,311
Members
447,901
Latest member
boy3hc2004

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back