Forum Discussion
Using FILTER as input to RANK
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:
- MATCH(A2, $A$2:$A$10, 0) finds the first occurrence of the category value in column A.
- INDEX($B$2:$B$10, MATCH(A2, $A$2:$A$10, 0)) returns the first value in column B for the given category.
- COUNTIF($A$2:$A$10, A2) counts the number of occurrences of the category value in column A.
- 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.
- 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.
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]) + 1The + 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!