I have multiple Sheets and would now like to add a statistics sheet which only displays the entry with the highest competition results per sheet. That’s easy. However I’d also like to display the highest scoring entry that meets certain genetic conditions. This is the part that gives me troubles as it has to be case sensitive, since A/A is vastly different from a/a (those are example values).
This is what I have right now:
=INDEX(Sheet1!$A$2:$A$100; MATCH(MAXIFS(Sheet1!$B$2:$B$100; Sheet1!$C$2:$C$100; "a/a"; Sheet1!$D$2:$D$100; "b/b"); Sheet1!$B$2:$B$100; 0))
Its working just fine except for the fact that it’s not case sensitive and I don’t know what I can do to make it case sensitive. The result should be “TestCase4” however it’s “TestCase1” due to this. I already tried using CHAR() instead in a desperate attempt but that of course didn’t work. And I can’t get stuff like e.g. REGEXMATCH() or EXACT() to work in a MAXIFS.
I have now added a new column next to the genetics, that just writes down a 1 if it’s a/a, 1.5 if it’s A/a and a 0 if it’s A/A and check for those columns in the MAXIFS clause but there has to be a nicer solution to this surely? I also would like to keep it somewhat biologically accurate so just changing the letters won’t do.
Grateful for any tips!
Yuri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
6
If you have Excel 365 you can use FILTER
=TAKE(SORT(FILTER(A2:C6,EXACT("a/a",C2:C6)),2,-1),1)
4
Using EXACT, replacing MAXIFS with MAX & FILTER:
=INDEX(
Sheet1!$A$2:$A$100,
MATCH(
MAX(
FILTER(
Sheet1!$B$2:$B$100,
EXACT(Sheet1!$C$2:$C$100, "a/a") *
EXACT(Sheet1!$D$2:$D$100, "b/b"),
""
)
),
Sheet1!$B$2:$B$100,
0
)
)
If there could be multiple results:
=FILTER(
Sheet1!$A$2:$A$100,
Sheet1!$B$2:$B$100 =
MAX(
FILTER(
Sheet1!$B$2:$B$100,
EXACT(Sheet1!$C$2:$C$100, "a/a") *
EXACT(Sheet1!$D$2:$D$100, "b/b"),
""
)
)
)
Update for Google Sheets
Google Sheets FILTER syntax is a bit different from Excel’s:
FILTER(range, condition1, [condition2, …])
So it works after removing the third parameter for [if_empty] (""):
2