I am trying to determine how often values appear in a row based on the lead value of the row. Essentially, if "A" is the first value of the row, what percentage of those "A" rows contain the value "B" in the subsequent columns, what percentage contain "C" in subsequent columns, etc.
Below is an example table with the leads and their partners
| Lead | Partner 1 | Partner 2 |
|---|---|---|
| A | B | C |
| A | C | E |
| B | A | E |
| C | B | A |
| A | D | B |
| B | C | E |
| A | B | D |
| B | E | D |
| C | D | B |
| A | E | C |
I want to output a table which stays what percentages of times values B-E appear for rows which start with A. In the example above, A is the lead 5 times, and B appears in those A rows 3 times, so the value is 60%
A Partners:
| Value | % |
|---|---|
| B | 60% |
| C | 60% |
| D | 40% |
| E | 40% |
Partners will always be unique, i.e. the same value wont appear in both columns 2 and 3 (e.g. no "BEE"). It doesn’t matter which column the partner appears in (2 or 3), it only matters if they appear in either column after where A is the lead.
I plan to have multiple "Partner tables" like the solution above, so I can also see how many times A&C-E appear in B-led rows, etc. But once I know how to make one table I can then make the others.
I tried a combination of IF and COUNTIF formulas, basically trying to say
If A2 contains A, then count the number of times B appears in the subsequent columns and divide it by the number of times A is in the lead.
=If((A2="A"),((COUNTIF(B2:C11,"B")/COUNTIF(A2:A11,"A")),0)
This of course results in skewed results because it counts how many times B appears in all rows, not just the ones which are lead by A. I’m having trouble limiting the count of Bs to only A rows.
Thank you!
>Solution :
You can set this formula:
=COUNTIF(FILTER(B:C,A:A = $F$1),F2)/COUNTA(FILTER(A:A,A:A = $F$1))
Or with BYROW for the four (or all you need) rows:
=BYROW(F2:F5,LAMBDA(each,COUNTIF(FILTER(B:C,A:A = $F$1),each)/COUNTA(FILTER(A:A,A:A = $F$1))))