Forum Discussion
Trying to return a result based on permutations from a data set
Hi,
I've been struggling with a solution to this for a bit and decided to come here for help (sorry if it isn't the best description of the problem). I have a data set that looks like the following:
I am trying to create a formula that analyzes permutations of 3 or 4 categories and returns a count value to a table if there are data values >2 in all of the categories. An example of this would be: For the permutation of categories A,B,C (Cells C2, D2, E2), check if each cell contains a value >2. Because all cells do not contain a value >2, nothing happens. For the permutation of categories I,L,Q (Cells K2, N2, S2) each cell contains a value >2. Ideally this returns a result to a table that that adds a count of +1 to the category I,L,Q.
I realize the number of 3 and 4 category permutations across 22 categories is huge, so in an even more ideal scenario the table only contains the 20 permutations with the highest count at the end of the analysis.
Ideally, the result table looks something like this:
Not even sure if this is super feasible with excel or if someone will return some code that will solve all my issues. Either way, thanks to the community for taking a look!
Good Luck!
P.S. I did come up with a really poor solution that worked for permutations across 2 categories, though it took a while.
4 Replies
- I am afraid but the question is not clear enough to answer. Can you elaborate ?
Excelonlineadvisor I have 22 categories labeled A-V (A, B, C, D, E, etc...). Each category has a list of 15,000 samples associated (1, 2, 3, 4..., 15000). I would like to look at whether or not there is a value associated with a sample number for each set of 3 or 4 categories. A smaller example is shown in the table below:
By using the following formula, I can return whether categories A, B, and C all contain a value >0 for any given sample.
=IF(AND(A>0, B>0, C>0), "yes", "no")
I would like to be able to create a formula that could look at all combinations of 3 or 4 categories and return the number of cases in which a value >0 is detected in all of the categories at once.
I'm capable of doing this the long and strenuous way, however, there are over 9,000 combinations of 3 categories that I would have to look at so I am wondering if there is a way to better automate this.
=SUM(BYROW(AG1:AI9240,LAMBDA(r,N(SUM(N(BYCOL(CHOOSECOLS(J2:AE8,XMATCH(r,J1:AE1)),LAMBDA(c,MAX(c)>D1))))=3))))
With Office 365 or Excel for the web this formula returns the intended result. In cell D1 is the number which is compared like this:
=IF(AND(A>D1, B>D1, C>D1), 1, 0)
The 9240 combinations are in range AG1:AI9240 in my sheet. The 0 and 1 in column AJ tell if all 3 categories are above the value from cell D1. E.g. categories A, B and C all have a value greater 5 therefore 1 is returned in cell AJ1. The above formula returns the sum from column AJ range AJ1:AJ9240 in cell H1 in this example.
