excelexcel-formulaformula

How to get part of the rows from a table based on the top 3 values of a specific column when same data is possible?


I have a sheet which I point failures, it is divided by failures (rows) and days (columns), I point the quantity of failures by the type of failure that day. So I used the vlookup to show me the top 3 failures of each day, but there is a problem, when it has the same quantity of different failures, it shows the same failure, not the next one with the same quantity.

I've tried ways to correct this, but everything I found didn't work, because it has variable quantity depending the day, and what I found use the COUNTIF trick, I can't define a value, so it doesn't work.

can anyone help me please?


Solution

  • There's probably an easier way, but this formula should work:

    =LET(day,Table1[Sunday],myarray,FILTER(CHOOSE({1,2},Table1[Failure],day),RANK.EQ(day,day)<=3),SORTBY(myarray,INDEX(myarray,,2),-1))
    

    enter image description here

    As you can see it returns an dynamic array containing the top 3 Failures in descending order, along with the number of failures.

    This should account for any ties as you can see in my example. However, it is possible for the returned array to be more than 3 rows if there is a tie for third place. Not sure if that's desirable or not.