Permutations & Combinations

Hello,
I am trying to create an automation or formula to run permutations based on test results. Some of the tests I run could be four columns some could be 10. I am trying to run the formula against the columns to identify the following answers:
- How can I run permutations against all of the results and determine the top 5 highest combo likelihood?
- How can I run the top 5 highest likelihood of Result 1, Result 2, Result 3, Result 4 based on historical data?
- How can I determine the day of the week that there certain combinations are most likely?
I am trying to determine different patterns in the results to identify if there are any variations occurring from different days, different results, etc.
Columns:
| Date | Result 1 | Result 2 | Result 3 | Result 4 | Concatenate Formula |
Answers
-
Are you able to provide some screenshots for context?
-
Here is some sample data to see:
-
-
Here are some details. I made them whole numbers to make it a little easier. I once had this all typed out in VBA but it was 80+ pages of text that I need to go back and look at.
-
I would suggest doing this on a separate sheet. The first part is going to be generating a list of each unique number in a column and then getting counts.
In the separate sheet, start with a text/number column called "Number". This will be manually populated with 1 starting in the first row 2 in the second, so on and so forth until you think you have enough rows to cover all unique numbers plus a buffer just in case. Using your most recent screenshot, that first column has 8 unique numbers in it. 1, 2, 3, 4, 5, 6, 7, and 9, so I would do 20 rows.
The next column over (which I will call "First Set") has this column formula in it:
=IFERROR(INDEX(DISTINCT(COLLECT({First Number Column}, {First Number Column}, @cell <> "")), Number@row), "")
This should give you a dynamic list of all of the unique numbers in that first column on the source sheet. Next column over ("First Set Counts") would be a COUNTIFS counting up how many times each number appears in the source column.
=IF([First Set]@row <> "", COUNTIFS({First Number Column}, @cell = [First Set]@row))
Repeat this for the rest of the columns. Then we can move to a third sheet. This one is where you can pull your various metrics. Top 5 would be something along the lines of the numbers 1 - 5 in the [Number] column then
=LARGE({First Set Counts}, Number@row)
to get the five highest scores.
Then there are a few different options for how to pull the numbers based on the scores that would depend on how you would want to handle a tie.
Help Article Resources
Categories
Check out the Formula Handbook template!