2

I have the following data in columns A and B. I want to filter the table by group 1 to return only the group 2 values as an array and the perform an operation on that array (in this case RANK)

If I do that as 2 steps, it works just fine

D2 formula is =TRANSPOSE(INDEX(FILTER($A$2:$B$12,$A$2:$A$12=A2), ,2)) (I put the transpose just to make readable) and the I2 Formula is =RANK.EQ(B2, D2:G2)

enter image description here

If I try to enter the function as =RANK.EQ(B2,INDEX(FILTER($A$2:$B$12,$A$2:$A$12=A2), ,2))

I get a #value cell error as an array, the same shape as the result of the filter

Can anyone help me avoid the intermediate step and figure out how to get this into a single formula? thanks in advance.

enter image description here

AMEND Thanks to @JvdV for a solution using an alternative aproach - I would be interested to know why I cant get around the error - what is it about the return value of one function that prevents me passing it to the next function? Thanks all

Improve this question
1
  • 1
    It looks RANK.EQ won't accept an array that doesn't tie back to a cell range in the second argument. =RANK.EQ(1, {1,2,3}) also doesn't work. I have had issues trying to use other functions like SUMIFS in a similar manner. Commented Feb 7, 2021 at 14:49

2 Answers 2

Reset to default
2

Maybe just use COUNTIFS():

enter image description here

Formula in D2:

=COUNTIFS(A2:A12,A2:A12,B2:B12,">"&B2:B12)+1
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for taking the time, your answer definitely answers " how to get this into a single formula". I would like to understand why I cant get around the error - what is it about the resturn value of one function that prevents me passing it to the next function
@JohnnieL, RANK.EQ() does appear to need a direct reference to a range of cell, not a calculated field, hence the error (that's my understanding of it at least). For example =RANK.EQ(5,SEQUENCE(5)) won't budge either.
yes - I think thats the conclusion - just frustrating not to be able to understand why ... I've trawled the microsoft docs no joy - many thanks for taking the time I do appreciate that
0

Yes, RANK.EQ definitely has usability / functionality / usefulness flaws. I learned about the COUNTIFS method, today.

Similar topic discussed: https://techcommunity.microsoft.com/t5/excel/using-filter-as-input-to-rank/m-p/3860460

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.

enter image description here

Cheers!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.