Using FILTER as input to RANK

This is a cool question... RANK (incl RANK.AVG and RANK.EQ) formulas in Excel are limiting as they do not support the use of inline First, using Excel Tables helps with readability and control. (Get to know Excel Tables)...

Yes, RANK.EQ definitely has usability / functionality / usefulness flaws. I learned about the COUNTIFS method, today.

Similar topic discussed: https://stackoverflow.com/questions/66088830/excel-value-error-passing-result-of-dynamic-array-formula-to-another-function/78374651#78374651

I like the use of COUNTIFs. Which can provide a proper rank and addresses the multiple issues listed above. First, for readability and transferability, use Excel Tables (so that you can reference cells by [column] and [@column]. Second, COUNTIFS allows up to 127 individual Criteria sets.

How? Think of COUNTIFS as multiple "AND" statements (e.g. A row is counted when: Column A meets Criteria A AND Column B meets Criteria B AND so on...). So, in the example above (assume you highlight all the data, including the headers Group1, Group2 and INSERT as Table). Now, create a 3rd Column called RANK and set the formula:

=COUNTIFS([Group1],[@Group1],[Group2],">"&[@Group2]) + 1

The + 1 on the end sets the RANK starting at 1 (instead of 0)

Problems with RANK.EQ / RANK.AVG a) doesn't support the (now common in many Excel formulas) **Dynamic Arrays** thus preventing the ability to perform a rank operation on a filtered/sorted sub-list (like the above example), and b) RANK ignores duplicates. Which seems sufficiently not satisfying the definition of Rank. When I think of real-world scenarios that might Rank test scores of students (grouped in different classes or grouped by specific tests). When students in a class have the same score on a test, it doesn't mean that there are fewer students in the class. A rank of 23rd of 30 students can be misleading (in a class of 50 students where the scores are A, B, C, D, F... a student could tell their parents that they had the fifth best grade in the class!). Or how about in Golf? When 2 players tie for 3rd place, is the next player on the leaderboard in 4th place? No (s)he's in 5th place (or RANK = 5).

Notice the added values at the bottom of the list for Group1 = B... This method proper ranks the Group2 value of "2" as 6th out of 6 entries.

Cheers!

TBCOL 

TBCOL 

I found a VERY Simple formula to address this:
https://exceljet.net/formulas/rank-if-formula

=COUNTIFS($V8:$V1958 <<Criteria Range (Obviously infinite, since this is COUNTIFS)>>,V9<<Criteria1>>,$AJ$8:$AJ$1958 <<Values Range>>,"<="&AJ9<<Current Row>>)

Hi TBCOL 

RANK, RANK.EQ and RANK.AVG expect a Reference as 2nd argument. So, if your FILTER spills say in B1, RANK(x, B1#) works no problem. If you don't let your FILTER to spill on the grid you get the issue

No idea what you want to FILTER & RANK so only examples below where my FILTER spills for demonstration only so you can see the values Ranked in H3, I3, K3 & L3:

In I3

=LET(
  Filtered, FILTER(Table1[Value], Table1[Product]=E3),
  XMATCH(Filtered, SORT(Filtered))
)