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:

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for context?

Pestomania

Here is some sample data to see:

Paul Newcome

What about some example outputs manually entered?

Pestomania

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.

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.