Using FILTER as input to RANK

[TBCOL]

I have a set of data with multiple categories, I want to write one formula that looks through all the data and returns the rank of a particular value within the subset of only its category

To do this I want to insert a FILTER function within a RANK function, using the FILTER to return a list of the values I want to RANK against

I can get the FILTER function on its own to return an overflow of the correct cell values, and I can write a separate RANK function on its outputs... HOWEVER excel will not let me insert the FILTER function directly in the RANK function just gives me a generic error pop-up with no specifics about why it isn't working

Have I run into a standard limitation of Excel and/or am I going about this entirely the wrong way?

[#2 · Accepted solution]

@TBCOL 

In Excel, you cannot directly use the FILTER function as an input to the RANK function. The RANK function requires a range of values as its first argument, and the FILTER function returns an array of values. That is why you are encountering an error when trying to use FILTER within RANK.

To achieve your desired result, you can use a combination of functions: INDEX, MATCH, and RANK. Here is an example formula:

=RANK(A2, INDEX($B$2:$B$10, MATCH(A2, $A$2:$A$10, 0)):INDEX($B$2:$B$10, MATCH(A2, $A$2:$A$10, 0) + COUNTIF($A$2:$A$10, A2) - 1))

Assuming your data is in columns A and B, and you want to rank the values in column B based on their category in column A.

Explanation of the formula:

1. MATCH(A2, $A$2:$A$10, 0) finds the first occurrence of the category value in column A.
2. INDEX($B$2:$B$10, MATCH(A2, $A$2:$A$10, 0)) returns the first value in column B for the given category.
3. COUNTIF($A$2:$A$10, A2) counts the number of occurrences of the category value in column A.
4. INDEX($B$2:$B$10, MATCH(A2, $A$2:$A$10, 0) + COUNTIF($A$2:$A$10, A2) - 1) returns the last value in column B for the given category.
5. The RANK function then ranks the value in cell A2 against the subset of values from step 2 to step 4.

Please adjust the range references in the formula according to your actual data range.

By using this approach, you can calculate the rank of a particular value within its category without directly using the FILTER function. The texts, steps and functions/Code were created with the help of AI for reasons of time.

Hope this will help you.

[#3]

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))
)

[#4]

Thanks for the response, your explanation of why it does not work is clear and concise

I prefer to avoid using overly complicated formula expressions so I ultimately elected to use the PERCENTILE.INC function instead of RANK and it worked just fine because it uses array input... strikes me as odd that two relatively similar functions operate so differently!

[#5]

@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>>)

[#6]

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-formu...

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