Using FILTER as input to RANK

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.