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

 

[mikerickson]

It sounds like you want Excel to list 23^6 possible combinations.

 

[Marc Wylie]

that's it....I think using the numbers I have above although I gues I could find replace after to get the data I wanted.

Any idea how?

 

[Dr. Demento]

Is this what you're looking for?? Mix And Match – Get all possible permutations of an unlimited number of lists in Excel | Struggling To Excel

Some other solutions here or here. Good luck!!

 

[pgc01]

Notice that if you'd generate all the permutations you'd not be able to write them in just 23 columns.

The latest excel versions have about 1 million rows and that would not be enough not even for a tenth of the total of permutations.


So, let's say you'd let the output spill to the columns to the right. You'll end up with numbers in 276 columns and more than 1,000,000 (1 million) rows.

Sorry, this makes no sense to me. Do you think you'd be able to do something with it?

 

[Dr. Demento]

He could put them in 23 columns (think of each cell/regulation having one of six possible responses). But you're right -- it's 72.7 million permutations (rows).

You could always do 10^6 permutations over 72 sheets Actually, if you made each permutation output into a single, concatenated string (pipe-delimited, say) so each cell has a single permutation for all 23 regulations, if you use 10^6 cells / column, you could make it fit. It's gonna be unwieldy though. I've tried with only 23 mil permutations and gave up.

pgc01 is right - what would you do with them all? If you need to calculate the number of possible permutations, Wolfram has a good explanation.

 

[Marc Wylie]

Ok 23 Million is obviously ridiculous so I need another solution, Which I think I know what it is but any pointers to help me how to do it would be appreciated (Thank you all for your comments so far!)

The problem is as follows:

Aircraft in my organisation are placed into one of six basic categories dependant upon the weight of the aircraft. However the regulatory authority has introduced regulation that states potential aggravating or mitigating factors can shift an aircraft up or down the category into a different regulatory regime. The regulation imposed for each category has 23 factors which must be accounted for (hence the 23^6 requirement as these are the possible permutations)

In my reading and musings regarding the problems I have accounted for Identification of hazards and quantification of those hazards to allow only those deemed a threat to be examined (I am an Aircraft Engineer focused on aviation safety and the MSc is Aviation / Nuclear focused)

The next problem to solve is how to quantify those threats against the regulation and the solution I have devised is to allow those carrying out the analysis to score each of the 23 regulations with a number that correlates to the baseline weight category of the aircraft. These numbers would then be taken at an average and dependant upon where the value generated falls between (for example 1 to <2.5 would be category 1 still) gives the analysts the basis to form a qualitative decision.

I need to develop a workbook that allows this to happen and gives the analyst a clear representation of what the result of the analysis is.

So there you go.

I have the answer (the 23 Million Permutations answer) but now need to work out the code to make it work on excel.

I guess I need a guide that's easy to use for how to do this stuff, rather than a detailed answer from you lovely people on here! Unless someone wants a mention in my dissertation acknowledgements

Thank you again for your comments so far and clearing up that plan A was a no go. Hope you can point me in a direction for Plan B.

Cheers

Marc

 

[Marc Wylie]

Sorry, In my excitement I read 23million and took that as the possible number of permutations. Just used the nPk formula from the wolfram link and its actualy 72,681,840 possible permutations.

Definitely plan B!!!

 

[mikerickson]

Are the 23 factors independent?

i.e. does the effect of factor #2 change if factors #1 and #3 are present and is that different than if #1 is present #3 isn't?

 

[Dr. Demento]

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!! Your 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, Risk Management Handbook (FAA-H-8083-2) Note, or here.