Ranking by group

[burgie10]

Hi there,

I wondered if you could help.

In columns N-P I'm trying to rank the average P1-P5 score by category. e.g. I would like the scores in column L to be ranked for all the names in column N.
In column O, I would only like to rank scores in column L according to their "MD" (Column D), and similar for "RD" in Column P.

I hope this makes sense... sorry if confusing... and thank you!

 

[SanjayGMusafir]

Hi there,

I wondered if you could help.

In columns N-P I'm trying to rank the average P1-P5 score by category. e.g. I would like the scores in column L to be ranked for all the names in column N.
In column O, I would only like to rank scores in column L according to their "MD" (Column D), and similar for "RD" in Column P.

I hope this makes sense... sorry if confusing... and thank you!View attachment 70579

The image you have shared does not show Column (L, M N, O, P etc.) thus makes bit difficult to relate to what you want to do. Please share a better picture with column headers.

 

[SanjayGMusafir]

But what I understand can be achieved, using a helper column that ranks the Average Values of Column L

 

[Peter_SSs]

Two things that would help in the future:

1.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

2.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


See if this is what you want.

22 08 02.xlsm
	D	E						K	L	M	N	O	P
1
2	MD	RD							P1-P5		TC	MD	RD
3	AD	AF							129		9	4	3
4	MP	AM							100		11	4	3
5	AS	BA							149		6	2	2
6	AD	EF							185		4	2	2
7	MP	AF							238		2	1	1
8	AS	AM							101		10	3	2
9	AD	BA							239		1	1	1
10	MP	EF							223		3	2	1
11	AS	AF							159		5	1	2
12	AD	AM							138		8	3	1
13	MP	BA							139		7	3	3
Rank

Cell Formulas
Range	Formula
N3:N13	N3	=RANK(L3,L$3:L$13)
O3:P13	O3	=SUMPRODUCT((D$3:D$13=D3)*($L3<$L$3:$L$13))+1

 

[SanjayGMusafir]

Thanks Peter_SSs for sharing useful information. I came on forum after a long time, so it helped me update my profile.