Jun 20 2023 11:30 AM
I'm trying to return the top 10 worst percentages with a LARGE function, and then in an adjacent column, use the INDEX MATCH functions to return which question corresponds with the % value. The problem is several questions have the same % value. What must I add to the function to ignore previously pulled values (questions)?
Thanks in advance!
Jun 20 2023 11:56 AM
=INDEX($A$1:$A$27,MATCH(1,($B$1:$B$27=F2)*($C$1:$C$27=E2),0))
You can try this formula along with helper columns. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=COUNTIF($B$1:B1,B1)
This is the formula in cell C1 in the example.
In column F is the LARGE function and column E is another COUNTIF which counts the values of column F.
Jun 20 2023 12:58 PM
This uses 365. If one sets out to exploit 365 to the full, the resulting formulas tend to be unrecognisable to anyone versed in traditional spreadsheet methods. In this case
= TAKE(SORTBY(table, percentage, -1), 10)
That is: sort the entire table by the percentage column descending and select the first 10 rows. For me 365 was worth the investment but then, I was delighted to dispense with concepts such as direct cell referencing (ignoring the 17 billion predefined names) and relative referencing (used to build lists).