Generating Outcomes Based on Probabilities

[DashboardNovice]

In the attached file I am trying to accomplish the following:

1. In cell E3, the user enters a probability
2. Column E, beginning with row 11, would randomly generate a "P" (for profit) or "L" (for loss).
3. The number of P's and L's generated would be based on the probability.

For example, if the probability of profit were 75%, column E would contain 75 P's and 25 L's. I have 100 occurrences which are numbered in column D.

Cells E5 and E6 contain a COUNTIF to verify the number of P's and L's which are randomly generated.

I'm getting the same number of P's and L's. Any suggestions?

 

[NARAYANK991]

Hi ,

That is because your COUNTIF formula is looking at Ps in both cases.

Change the second one to look at Ls.

Narayan

 

[DashboardNovice]

Hi ,

That is because your COUNTIF formula is looking at Ps in both cases.

Change the second one to look at Ls.

Narayan

I can't believe I overlooked that. Thank you.

I made the change, however for some reason, the outcomes of the P's and L's are not matching the specified probability. They are close, but I am trying to get it to match the probability exactly.

 

[NARAYANK991]

Hi ,

It will be very difficult with a simple formula to match the target figure exactly , because the function used is RANDBETWEEN or RAND.

We will need to have checks to count how many Ps and Ls have already been generated , for every remaining cell in the range , so that when ever the number of Ps threatens to go beyond the target , the probability is skewed in favour of Ls.

Please wait while a robust formula is arrived at ; VBA might make this an easier proposition.

Narayan

 

[bosco_yip]

1] A formula with helper way

2] See as per attached file.

Regards
Bosco

 

[DashboardNovice]

I have a better idea. What about using RANDBETWEEN (1,100) but making it so that:

1. there are no duplicate numbers
2. each number from 1 - 100 appears in a range of 100 cells

I did this once but I can't find that spreadsheet.

 

[DashboardNovice]

1] A formula with helper way

2] See as per attached file.

Regards
Bosco

Thank you for your solution, but is there a way to make the P's and L's appear randomly instead of P's first, then L's first?

 

[bosco_yip]

Thank you for your solution, but is there a way to make the P's and L's appear randomly instead of P's first, then L's first?

Press F9 for recalculation and check "P/L result" column E

Regards