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!