Huge Combination Conudrum - Please HELP

[Marc Wylie]

Hi All,

I am currently in the idle of my MSc Dissertation and have a huge problem. I know what I need Excel to do for me but I have n idea where to start.

I need to list all possible combinations of the following:

six different categories of aircraft to which I have allocated the following numbers (why these numbers is not important to solving the problem):

1
3
7
17
27
37

There are 23 regulations which must each have the applicable category dependant upon the aircrafts attributes placed into it.

So my problem is I need Excel to calculate and list every possible combination of the six aircraft categories over 23 columns starting like below:

1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 3
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 3 3
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 3 3 3

and on and on until infinity and beyond (well until all possible combinations have been calculated. I can then remove duplications and use the data for what I need.

Please be gentle as I am a complete excel novice but I need help in solving this pretty quickly so I don't waste to much time on data entry and can get on with the important bit of writing my dissertation!

Many thanks in advance for all your help and time.

Marc Wylie

 

[shg]

Mike's question seems relevant.

Aside from that, can you climb to angels ten and explain what you're trying to do?

 

[Marc Wylie]

Sorry - I misunderstood - you're not dealing with permutations (defined as the number of sub-sets that can be created including sub-sets of the same items in different orders; btw, that equals the 76 million; the 23 million I mentioned was a previous project I attempted) -- you're dealing with something much worse!! You're n is 6^23 = 7.9*10^17. That is, you have six possible responses for each of the 23 categories, yes? If so, that's really untenable to list all possibilities.

Besides being untenable to list out, I think the approach has might have some issues -- 1) weighting (probabilty of occurence, severity of outcome) which is almost never a straight average; 2) you would have to determine your cut points (you mention 1 to <2.5 - it could be anything); 3) I think you're approaching this backwards - models do their best to describe the real world, so you need real world data to determine the calculations, including weighting, interactive effects, etc. You would need to define your cases, match them with controls, measure inputs and outcomes - that's where I'd start. However, like you said, this is a qualitative paradigm; it will have a qualitative approach (not quantative).

Cursorily poking around, I found this article which may be a spring board - Probabilistic Causal Analysis for System Safety Risk Assessments in Commercial Air Transport or here.

Hey,

I have done a lot of work in the background regarding the weightings etc, and where I am is a semi quantitative approach to give a better representation of the situation to the analyst.

If you want the full extent of the problem:
weight category one is 0 200g (which I have spilt into two subsets f 100g)
Two is 200g - 1.8kg (split into four subsets)
three is 1.8 - 20kg (split into ten subsets)
four is 20kg - 150kg (split into 10 subsets)
five is 150kg - 600kg (split into 10 subsets)
six is 600kg - 2000kg (split into 10 subsets)

The intent is not to deal with risk which can be dealt with through design or some other form of control but rather deal with the residual risk of the peculiarities of a system by applying a pre prescribed level of regulation to a problem (the 23 different types of regulation with six possible levels determined by the baseline aircraft weight)

I understand the probabilistic or deterministic methods of risk analysis and I am using those in the analysis phase of identifying the individual hazard factors and rank them in in terms of weighting. Those which are outside of the tolerability region must be considered for another means of reduction or control, and those which are tolerable but not reduced ALARP may be considered for analysis using the method I am proposing. (this part is a qualitative decision that must be made by the panel carrying out the analysis). So now you know what I am setting out to achieve. I guess I am off out into the land of VBA programming for dummies and making it work. I'll be sure to let you all know how I get on!

Thanks

Marc

 

[Dr. Demento]

Best wishes, Marc. You've obviously done your homework and you've got a hike in front of you.

 

[Marc Wylie]

Thank you Dr D.

If you do have any recommendations for reference material regarding building functional workbooks it would be greatly appreciated.

 

[Dr. Demento]

Afraid not -- I'm a VBA noob too.

I was thinking though - what about a marriage between MATLAB (something which I'm sure you're conversant with) and VBA - something that allows for user interface and output via Excel but MATLAB does all the heavy lifting (like this).