Jun 29 2023 10:37 PM
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?
Jun 29 2023 11:21 PM
SolutionIn 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:
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.
Jun 29 2023 11:23 PM
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))
)
Jul 02 2023 06:10 PM
Mar 15 2024 06:07 AM
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>>)
Apr 23 2024 12:42 PM
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!
Jun 29 2023 11:21 PM
SolutionIn 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:
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.