Probability Generator

[wrx123]

Hi,

Can anyone help? Basically, I have a list of projects and dates upon which these projects are likely to be undertaken. At the moment the model assumes 100% of these projects actually happen. What I want to do is insert a column that will use a different percantage that I can input mannually next to each date and generate a random selection of the dates from the date column which reflects the probability of the projects happening.

So it would look like this:

P1 01/02/11 10%
P2 01/09/12 100%
P3 01/06/13 50%

And what I need if for excel to calcualte a random selection of the projects actually happening based on the probabilities and creating an output for each of either say 1 or 0 as in yes it will happen or no it wont.

Any help would be amazing thank you

 

[xenou]

This formula can go in the cell next to the percentage you've supplied:
(Let's assume it is in D2):

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


Then fill down.
Results (1-->success, 0-->failure):

-----------------------------
| A  |         B | C    | D |
-----------------------------
| P1 |  1/2/2011 | 10%  | 0 |
| P2 |  1/9/2012 | 100% | 1 |
| P3 |  1/6/2013 | 50%  | 1 |
| P4 |  1/7/2013 | 40%  | 0 |
| P5 |  1/8/2013 | 12%  | 0 |
| P6 |  1/9/2013 | 45%  | 0 |
| P7 | 1/10/2013 | 89%  | 1 |
| P8 | 1/11/2013 | 9%   | 0 |
| P9 | 1/12/2013 | 60%  | 0 |
-----------------------------


The only drawback is it recalcs a lot since Rand() is volatile. If you want to hard code the results in I'd put a double click in the first cell (in D2). Then use a double click event to trigger a refresh when you want a new trial:

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
    
    [COLOR="Navy"]If[/COLOR] Target.Address = "$D$2" [COLOR="Navy"]Then[/COLOR]
        Cancel = True
        Target.FormulaR1C1 = "=IF(RAND()<=RC[-1],1,0)"
        [COLOR="Navy"]If[/COLOR] Len(Target.Offset(1).Value) > 0 [COLOR="Navy"]Then[/COLOR]
            Range(Target, Target.End(xlDown)).FillDown
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

<a href="http://northernocean.net/etc/mrexcel/20110525_sample.zip">SAMPLE WORKBOOK</a>
md5sum (zip file): c3b3fba4d5a51fc3f00801d8656751bd
sha256sum (zip file): 8a405d8e14859576902efec9b5d663053618f58649cfa8eff30916985d337e04

ξ

 

[wrx123]

That worked a treat. Thank you