Hi there,
I wondered if you could help.
In columns M-O I'm trying to rank the average P1-P5 score by category. e.g. I would like the scores in column K to be ranked for all the names in column M.
In column N, I would only like to rank scores in column K according to their "MD" (Column C), and similar for "RD" in Column O.
I hope this makes sense... sorry if confusing... and thank you!
not sure what MS excel version you have but you can try this formula in M3 copy across, it should spill down automatically.
Formula:
=XLOOKUP(SORT($K$3:$K$14,1,-1),$K$3:$K$14,B$3:B$14,0,0)
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
Hi there, thank you for responding so quickly, really appreciate it.
I don't think that works, that formula returns a name but I was hoping numbers would appear in column M-O.
So for row 3 column N, I wanted to know what rank Alan Francis is within Romeo Cox's MD category. Similarly, in column O I wanted to know what rank Alan Francis is within Kellie Lowe's 'RD' category.
You'll notice there are 4 names which are within the RD category "Kellie Lowe"... so I'm after a rank of 1-4 between Alan Francis, Ida Velazquez, Jannie Becker & Carmella Pope.
I hope this makes a little bit of sense!!
In MD column N3 and drag down
=SUMPRODUCT((C3=$C$3:$C$14)*(K3<$K$3:$K$14))+1
In RD column O3 and drag down
=SUMPRODUCT((D3=$D$3:$D$14)*(K3<$K$3:$K$14))+1
...and (edit) just for completeness
In total company column M3 and drag down
=RANK(K3,$K$3:$K$14,0)
Last edited by Croweater; 08-01-2022 at 08:44 PM.
For users
Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2021
Excel 2021 for Mac
Excel for iPad
Excel for iPhone
Excel for Android tablets
Excel for Android phones:
This formula produces the entire table at once
=SORTBY(B3:D14,K3:K14,-1)
Last edited by HansDouwe; 08-02-2022 at 12:14 AM.
Did any of the solutions offered solve your problem?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks