1 or 0 outcome based on probability?

sashoy

New Member
Joined
May 31, 2012
Messages
5
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.
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
 
Upvote 0
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)
 
Upvote 0
=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?
 
Upvote 0
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)
 
Upvote 0
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. :(
 
Upvote 0
Ok thanks to all who replay here, it seems that I have found solution based on your GREAT help!:biggrin:
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top