Cummulative Probability Macro

[RKettle]

Good Day and Seasonal Greetings to All.

I am new to the forum, and I am looking for some assistance with macro functionality

I would like to build cumm probability macro, that looks to generate a cumulative probability plot for 3 output variables, based on the range of potential outcomes of 14-input variables.

The 14-off input variables have 3-possible states ("1" "2" or "3").

I am looking to generate a macro sheet that will generate these inputs systematically and create a register of outcomes, so that I can the generate these cumm prob plots.

The calculation model is fully constructed and operational on a discrete basis, and I am now simply looking to automate the case generation process

It would seem that this should not be a hard thing to construct, but to be fair I have no idea where to start and while pretty adept in pivots, formulas etc I have no idea about macros.

Thanks and Regards
Richard

[mrice]

You could try something like the following but you will run out of rows as 3^14 is just short of 5 million.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

[RKettle]

Martin
Thanks very much for your reply, I can sort of visually see whats going on.
Excuse my ignorance, but what how is it linked to the workbook? Where do the input cell references go in, and where does it pick up the 3-output variables etc?

I have no issue putting a donation into your sponsorship fund. You need to send me your paypal details though.
Regards
Richard

[kalak]

Wasn't that obvious to me what you want. A sample output would be instructive.
But if you do want a comparable output to the code in Post#2 you can try opening a blank worksheet and just run the following code.
It took me about 40 seconds to calculate and fill the the first 14 columns of a 1,048,578 row worksheet.
If you want more rows filled it can either continue with more columns on the same worksheet or else overflow onto another sheet.

Please Login or Register  to view this content.

"The numbers add up to nothing" (Neil Young)

[RKettle]

Thanks kalak...
to better define the question...

I am looking to build a macro that can enter 1's, 2's or 3's into a column of cells (eg: A1,A2,A3,A4,A5,A6 et al...) as the input variables, and then read and record the results from a different range of 3-cells (eg: D1,D2,D3), recording these results in a table.

The macro should sequentially enter every combination of inputs (1's, 2's and 3's) and compile the results.
My intention with the data is to draw a graph of the cumulative probability of the outcome falling above or below a certain level.

The workbook is quite extensive, but suffice to say that it is already set with the equations that link the input cells to the output cells. All I need to do is enter the input combinations and read the outputs.

Looking at relative impact of the inputs I am in a position to limit the assessed inputs to 12 which will also help limit the size of the file generated.

Once again thanks for your help, I hope the above defines the problem more correctly
I am currently experimenting with the coding above... thanks a lot
Regards
Richard

[mrice]

It would be good if you could attach a copy of your workbook. Making a guess as to what it might look like you could try ...

Please Login or Register  to view this content.

[RKettle]

Thanks for your reply. Its great to have such assistance at hand.

By way of example I had attached a workbook which mocks up what I am trying to achieve, with an arbitrary equation linking the inputs/outputs.

I have tried to add the macro, but I must have something wrong in the syntax or?

Thanks again.
Richard

[RKettle]

Martin
Sorry mate... I have it working now (attached).
I will reduce it to 12-variables only.

How do get it to post the results into a new sheet each time the results are generated?

Thanks and Regards
Richard

[mrice]

Please see the attached. I've taken the liberty of adding some extra features to the code to make it run faster. These include turning the screen updating off and to compensate I've added a progress indicator in the status bar. The results will appear on Sheet3.

[RKettle]

Martin
Thank you very much for your time and effort, the macro provides the solution. Grateful for your assistance, and clicking the donation link now.

I wish you every success for 2015
Regards
Richard

[RKettle]

mate... donation page says no longer accepting donations...

Let me know

Regards
Richard

[mrice]

Thanks for letting me know.

I've created a new page referring to my next run (See below)

Thanks for your generosity - Happy New Year.